How to create measure to calculate grouped averages on different levels

[Bamse]

Hi!

EDIT: Added more clarification in both pictures.

I am rather new to Power-BI/DAX and created a report that I believed was producing very nice results until I actually checked them...
Averages are calculated incorrectly and now I am trying to create a measure which returns a correct grouped average for each level in a hierarchy.
I found a few similar toppics but noting seems to acutally resolve the issue I am dealing with, I only seem to be able to get the measure to function correctly for the lowest level.

I've created the following simplified dataset to experiment on.
To the left the input data is shown, to the right the desired grouping and result is shown with some colour highlighting to show how things are related.
Note that in this model all data is in a single table, in reality items reside in related tables.

Below it can be seen what I want to achieve in the report
The Column chart has a measure which calculates the Y axis.
When I drill through the diagram (which is based upon the shown hierarchy) it should produce the correct average as shown above.

I've been getting 'a bit further' with 'GROUPBY', but as mentioned only for the first level.

[Anonymous · Accepted solution]

Hi @Bamse ,

You get your results with a measure that looks like this: 

With your data this would produce the following:

MyAvg = 
var __Player = VALUES(Sheet1[Player])
var __Team = VALUES(Sheet1[Team])
var __Club = VALUES(Sheet1[Club])
var __Country = VALUES(Sheet1[Country])

var PlayerLevel = GROUPBY(Sheet1, Sheet1[Country],Sheet1[Club], Sheet1[Team], Sheet1[Player], "MyAvg", AVERAGEX(CURRENTGROUP(),Sheet1[Score]))
var TeamLevel = GroupBY(PlayerLevel, Sheet1[Country],Sheet1[Club], Sheet1[Team], "MyAvg", AVERAGEX(CURRENTGROUP(), [MyAvg]))
var ClubLevel = GroupBY(TeamLevel, Sheet1[Country],Sheet1[Club], "MyAvg", AVERAGEX(CURRENTGROUP(), [MyAvg]))
var CountryLevel = GroupBY(TeamLevel, Sheet1[Country], "MyAvg", AVERAGEX(CURRENTGROUP(), [MyAvg])) 

return 
switch (True, 
HASONEVALUE(Sheet1[Player]), Calculate(AVERAGEX(PlayerLevel, [MyAvg]), Sheet1[Player] = __Player),
HASONEVALUE(Sheet1[Team]), Calculate(AVERAGEX(TeamLevel, [MyAvg]), Sheet1[Player] = __Team),
HASONEVALUE(Sheet1[Club]), Calculate(AVERAGEX(ClubLevel, [MyAvg]), Sheet1[Player] = __Club),
HASONEVALUE(Sheet1[Country]), Calculate(AVERAGEX(CountryLevel, [MyAvg]), Sheet1[Player] = __Country)
)

I hope you translate to your actual model.

Jan 

if this is a solution for you, don't forget to mark it as such. thanks 

[Bamse]

Thanks Jan!

That's a great solution!
I had to make a tiny modification in the Switch clause to get this actually working in my test model.
I am looking forward to building this into my production model 🙂

Changes to switch clause for future readers: