Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
maxduff59
Helper II
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 :

Capture d’écran 2022-11-11 à 15.19.42.png

 

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

 

1 ACCEPTED SOLUTION
MFelix
Super User
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] ) )
))

MFelix_0-1668271873881.png

 

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

5 REPLIES 5
MFelix
Super User
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] ) )
))

MFelix_0-1668271873881.png

 

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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. 

maxduff59_0-1668596914276.png

 

@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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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 : 
CodeCode

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.

 

ResultResult

 

Sorry, to bother you again 😕 

@MFelix 

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:

MFelix_0-1668715197854.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

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

October NL Carousel

Fabric Community Update - October 2024

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