Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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;
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
Solved! Go to 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.
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.
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
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |