Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi everybody,
I am a newbie on Power BI so I am facing many setbacks doing my dashboard. I have a dataset which looks like this :
I would like to create a measure which would give me the percent of average. To be more accurate, I would like to compute the distance_covered for each game (no trainings) (distance_match1 would be 2400+2300=4700, distance_match2 = 3975 and distance_match3 = 3930). Then I would like to compute the average of these values (average = (4700 + 3975 + 3930)/3 = 4201.6).
And finally I would like to have the percentage of my value compared to percentage (ex: for match1 : 4700/4201.6 = 1.12 = 112%).
Is it possible to do it using only one measure ?
Thanks in advance for your time and your help
Solved! Go to Solution.
Hi @maxduff59 ,
Try the following measures:
Average Values =
AVERAGEX (
SUMMARIZE (
FILTER ( Games, LEFT ( Games[part_of_the_game], 4 ) = "game" ),
Games[id_game],
"TotalDistance", SUM ( Games[distance_covered] )
),
[TotalDistance]
)
% Values =
IF(ISINSCOPE(Games[id_game]),
DIVIDE (
SUMX (
SUMMARIZE (
FILTER ( Games, LEFT ( Games[part_of_the_game], 4 ) = "game" ),
Games[id_game],
"TotalDistance", SUM ( Games[distance_covered] )
),
[TotalDistance]
),
CALCULATE ( [Average Values], ALL ( Games[id_game] ) )
))
Be aware that I have made the filter directly on the measure but this can also be achieved in different ways.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @maxduff59 ,
Try the following measures:
Average Values =
AVERAGEX (
SUMMARIZE (
FILTER ( Games, LEFT ( Games[part_of_the_game], 4 ) = "game" ),
Games[id_game],
"TotalDistance", SUM ( Games[distance_covered] )
),
[TotalDistance]
)
% Values =
IF(ISINSCOPE(Games[id_game]),
DIVIDE (
SUMX (
SUMMARIZE (
FILTER ( Games, LEFT ( Games[part_of_the_game], 4 ) = "game" ),
Games[id_game],
"TotalDistance", SUM ( Games[distance_covered] )
),
[TotalDistance]
),
CALCULATE ( [Average Values], ALL ( Games[id_game] ) )
))
Be aware that I have made the filter directly on the measure but this can also be achieved in different ways.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThanks a lot for your solution it helped me a lot. However, I would like to do the same to compute the MAXIMUM of all this game so I tried to replace AVERAGEX() by MAX() but it creates an error.
@MFelix
Do you have an idea of how I could solve this problem ?
Hi @maxduff59
You need to replace the AVERAGEX by the MAXX function, that will calculate the maximum value over a table.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThanks a lot. I am really sorry but now I am facing a new problem and I would like to know if it was possible to ask you again. I have another colum named "Duration" and I would like to make the average of the values where the sum of the first and second half is above 60 minutes.
I tried that :
But, it is showing the values of games with duration above 60 minutes which is a good step but it is not computing the average of the values.
Sorry, to bother you again 😕
Hi @maxduff59 ,
No bother at all, in this case your issue is regarding the way you are calculating the total values of the metric.
Altough I don't have the details of the data try the following code:
Average Values =
AVERAGEX (
FILTER (
SUMMARIZE (
FILTER ( Games, LEFT ( Games[part_of_the_game], 4 ) = "game" ),
Games[id_game],
"TotalDistance", SUM ( Games[distance_covered] ),
"TotalDuration", SUM ( Games[duration] )
),
[TotalDuration] > 60
),
[TotalDistance]
)
This may need some adjustment on the data but overall would give something like this:
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCheck out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
109 | |
107 | |
92 | |
67 |
User | Count |
---|---|
162 | |
133 | |
132 | |
93 | |
91 |