Forum Discussion
Average of Average Pattern Inaccuracies
hnguy71 You can account for the different granularities using the ISINSCOPE function. So something like a SWITCH TRUE statement combined with ISINSCOPE functions should allow you to get exactly what you want.
The orginal post has a sample pbix for you to explore.
In the sample model, I have three different granularities named GEO. Geo0 would be by hemipshere, GEOL1 would assume to be Country, and GeoL2 would assume to be City. Of course, this is just sample data, but in a real world scenario you'd also have other granular attributes region, division, etc.
Interestingly, if I plug the data into an excel file, the average instead is 93.19% which is a totally different number.
Since there is so many ways to dissect the "correct" average, I may have to ask the business stakeholders to determine which average calculation / method is the correct one.
hnguy71 For the GeoL table visual you can use the following formula. I also attached an updated PBIX file. Missed that you attached one.
Avg.Of.Avg2 =
VAR _Table = SUMMARIZE( 'Dim_Employees', [GeoL1], "Average", [t.Average] )
VAR _Return = AVERAGEX( _Table, [Average] )
RETURN _ReturnThank you again for looking into this. While your measure does account for one granularity, the average and its total changes at different granularities.
So in this case, if your measure output at total is 90.23%, I would expect all roll-up granularities to also average out to the same number.
If I adjust the measure to a lower granularity, say GeoL2 my expected output should still be 90.23%, except this time I'm returning 86.27%:
Avg.Of.Avg2 = VAR _Table = SUMMARIZE( 'Dim_Employees', [GeoL2], "Average", [t.Average] ) VAR _Return = AVERAGEX( _Table, [Average] ) RETURN _ReturnNot really sure what the formula engine is doing to come up with different numbers. Perhaps I've never noticed it before. hehe, I guess as you mention before, it just depends on what "correct" number the business would like to see it as.
We'll table this for now, but appreciate your assistance!hnguy71 You can account for the different granularities using the ISINSCOPE function. So something like a SWITCH TRUE statement combined with ISINSCOPE functions should allow you to get exactly what you want.