cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Bamse
Frequent Visitor

How to create measure to calculate grouped averages on different levels

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.
Question Excel Colorized.png

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.
Report_correction.png

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

1 ACCEPTED SOLUTION
JustJan
Responsive Resident
Responsive Resident

Hi @Bamse ,

 

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

2020-03-31 21_19_00-Window.png

With your data this would produce the following:

2020-03-31 21_17_16-Window.png

 

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 

View solution in original post

2 REPLIES 2
JustJan
Responsive Resident
Responsive Resident

Hi @Bamse ,

 

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

2020-03-31 21_19_00-Window.png

With your data this would produce the following:

2020-03-31 21_17_16-Window.png

 

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 

View solution in original post

Bamse
Frequent Visitor

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:
Switch Clause.png

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors