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.
Thank 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 _Return
Not 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.