Trying to rank a measure

[PetyrBaelish]

I have a measure called AVG Points, formula below:

AVG Points = SUM(Results[Points]) / COUNT(Results[Player])

I then use this measure on a visual (see first two columns in image below) so I can see every player and their AVG points within a season (every time a player plays a 'tournament', that tournament is part of a season).

I have then tried to rank the AVG Points column (the prupose of this is to be able to filter the visual to just the top 5), however as can be seen below, the ranks aren't aligned to the AVG Points column (given the AVG points column is sorted, I'd expect row 1 to be rank 1, row 2 to be rank 2 etc.)

Here are my formulas for the 2 measures I have tried:

AVGPointsRank = RANKX(ALLSELECTED(Results),CALCULATE(SUM(Results[Points]) / COUNT(Results[Player]),ALLEXCEPT(Results,Results[Player],Tnmt[Season])),,DESC,Dense)

TestRank = RANKX(ALLSELECTED('Results'),[AVG Points],[AVG Points],DESC)

A couple of extra points to explain things. On my "AVGPointsRank" measure I've replaced the reference to the AVG points measure with the formula used for the measure as my expression part of the RANKX formula - it semeed I made progress in doing so, but not actully sure if that's the case or not.

With regards to the data - most of this is coming from the Results table where it's recorded that on, for example: week 1 Ben got 7 points, week 2 Ben got 9 points (which gives him an average points of 8.

There is another table called "Tnmt" which holds a column called "Season". This holds, for example, that weeks 1 & 2 are part of the 2018 season, then weeks 3 & 4 are part of the 2019 season and so on. I'm not sure I want/need this as part of my measure as my page level filters/slicers should take care of this (but once more, I felt like I made a bit of progress by including this).

Looking at the answers I have it feels like the season element isn't being taken into account (although the visualisation is on a page being controlled by a slicer - the AVG Points column is correct as per the slicer, but not the rankings)

[v-danhe-msft]

Hi @PetyrBaelish,

Could you please share a sample file to have a test?

Regards,

Daniel He

[PetyrBaelish]

Here's a link to my report.

Please look in the "Current" tab, at the right hand side visual.

https://www.dropbox.com/s/b0erxvn12kl1fj7/DUMMY%20PKR%20Reports%20V2.pbix?dl=0

Note the "Season" tab contains a slicer that filters the Current taband i believe this isn't applying to the right hand side visual for "AVG Points" (but is applying to the left hand visual "League)

[v-danhe-msft · Accepted solution]

Hi @PetyrBaelish,

Based on my test, you could use below formula:

AVGPointsRank = RANKX(ALLSELECTED('Results'[Player]),'Results'[AVG Points],,DESC,Dense)

Result:

You could also download the pbix file to have a view.

Regards,

Daniel He