in pp-3-superstore_ after - tab 12 cell B31 is TExt Average of Order Amount which refers to the implicit measure Average of Order Amount
ISSUE: the average returned is not confirmed with my checking on the status bar - can you explain the difference?
the Jan grand total (average) matches your file and shows 1,589
when I use the average calculation on the range c12:c23 on the status bar, it yields 2150.6
my function in c12 for Jan for Alberta,
=CUBEVALUE("ThisWorkbookDataModel","[Regions].[ProvinceID].["&$B12&"]","[Dates].[MonthName].["&C$11&"]",$B$10,Slicer_Year3)
my function for grand total Jan in c24 is
=CUBEVALUE("ThisWorkbookDataModel","[Regions].[ProvinceID].[all]","[Dates].[MonthName].["&C$11&"]",$B$10,Slicer_Year3)
oh! and Slicer selection is 2011
if I clear selection in the slicer, the grand total for Jan is 1970 but the status bar average is 21074.7
Hi Dianne,
This is an 'average of averages' problem. The grand total average figure is not the average of the regional averages shown in rows 12:23, it is the average of all underlying values that make up the region averages.
In other words, imagine you just show the grand total average, this would be calculated on each underlying transaction. It wouldn't first group those transactions into regions and then average those regions before averaging the region averages!
Hope that makes sense 🙂
Mynda
