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!!
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!!