cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## Compute the percent of average of a sum with conditions

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 ?

1 ACCEPTED SOLUTION
Super User

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ês

5 REPLIES 5
Super User

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ês

Helper II

Thanks 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 ?

Super User

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ês

Helper II

Thanks 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 :
Code

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.

Result

Sorry, to bother you again 😕

Super User

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ês

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors