Compute the percent of average of a sum with conditions

[maxduff59]

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

[MFelix · Accepted 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.

[maxduff59]

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 ? 

[MFelix]

Hi @maxduff59 

You need to replace the AVERAGEX by the MAXX function, that will calculate the maximum value over a table.

[maxduff59]

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 😕 

@MFelix 

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