Average of Subtotals in Pivot Table

M

millenium

Joined
Aug 14, 2014
Messages
1
Reaction score
0
I have a monthly report where I use the pivot table to get the summary of averaged sales of several regions. But the average of 8 regions subtotal does not match the grand total average of the pivot table. Is there way to get the calculate to be the same?

Region Count of sales Average sales
Boston 1 62
Chicago 5 68.8
Denver 7 62.8
Las Vegas 4 56.25
NorCal 19 84.26
NorWest 1 60
SoCal 9 79.67
Texas 28 75.04
Grand total 74 75

But when I average the subtotal 62 + 68.8 + 62.8 + 56.25 + 84.26 + 60 + 79.67 + 75.04 / 8 = 68.61
The average of the subtotal 68.61 is not the same as the Grand Total average of sales 75

I know it is due to the count of sales are different from the subtotal and the grand total.
Is there way to use the pivot table or calculate to get the subtotal to be the same as the grand total for average sales?
Thank you!!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to ignore a column of numbers in Grand Total for rows 1
Calculate Average in pivot item 6
pivot table and grand average 4
Pivot Table Sales Projections column 2
Pivot Table -- Add column to average Grand Total 1
Negative numbers in pivot table 1
average of sumed data in pivot tabel 3
Calculation of Gross Margin in a pivot table 1

Top