Average of Top N values

[seanmcc]

Hi all,

I am looking for some help creating a measure to calculate the average value of top 5 per player which need to be filtered.

I have created a measure to calculate a player's max speed but needs to be filtered;

Max Speed = CALCULATE(MAX('GPS Data'[Maximum Velocity (m/s)]), 'GPS Data'[Date] > DATE(2017,07,03), 'GPS Data'[Matchday (+/-)] <> "MD" && 'GPS Data'[Matchday (+/-)] <> "PSF" && 'GPS Data'[Matchday (+/-)] <> "Off Season")

I now need to use this filtering to assess the player's top 5 'Maximum Velocity (m/s)' values and average them.

This is the measure I have attempted;

CALCULATE(AVERAGE('GPS Data'[Maximum Velocity (m/s)]),TOPN(5,'GPS Data','GPS Data'[Name],DESC), Dates[Date] > DATE(2017,07,03), 'GPS Data'[Matchday (+/-)] <> "MD" && 'GPS Data'[Matchday (+/-)] <> "PSF" && 'GPS Data'[Matchday (+/-)] <> "Off Season")

Below is a sample of the maximum velocity this player acheived, sorted highest to lowest.

My desired outcome is the average of the top 5 = 10.04

Session ID	Maximum Velocity (m/s)	Average of top 5
07/11/2018Player 1	10.33	10.33
23/08/2018Player 1	10.33	10.33
11/09/2018Player 1	9.99	9.99
07/08/2018Player 1	9.8	9.8
15/08/2017Player 1	9.75	9.75
20/11/2018Player 1	9.75	9.75

Thanks in advance

Sean

[v-kelly-msft · Accepted solution]

Hi @seanmcc ，

I created a sample table and get the top 4 as an example,see below:

First go to query editor ,add an index column;

Then create 2 measures as below:

Maximum Velocity (m/s) = 
var a = SUMX(GROUPBY('Table','Table'[Player]),RANKX(ALLSELECTED('Table'),CALCULATE(SUM('Table'[Speed])),,DESC,Dense))
return
IF(a<=4,MAX('Table'[Speed]),0)

Average of top 4 = AVERAGEX(FILTER(ALL('Table'),'Table'[Player]=MAX('Table'[Player])&&'Table'[Maximum Velocity (m/s)]<>0),'Table'[Maximum Velocity (m/s)])

And you will see:

For the related .pbix file,pls click here.

 
Best Regards,
Kelly

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

[maddogp]

Hi Kelly,

Thank you for posting this.  When I applied it in my setting I noticed that when it is calculating the maximum velocities, it is not considering that two max velocities that are the same should be counted as two of the four max.  In your example, the avg of top 4 should be (10.33+10.33+9.99+9.8)/4=10.11. However it appears to be disregarding the second 10.33 and calculating the avg as (10.33+9.99+9.8+9.75)/4=9.99.

How would you modify the measures to calculate the average that include duplicated maximums?

Thanks

[amitchandak]

@seanmcc , refer to 2nd  post and pbix in last post

https://community.powerbi.com/t5/DAX-Commands-and-Tips/calculate-average-for-Top-3-5-10/td-p/904215

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale