Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
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:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
19 |