topic Re: Returning Top and Bottom 10 Ranked Items in DAX Commands and Tips

Returning Top and Bottom 10 Ranked Items

RNZALR — Tue, 24 Jan 2023 06:30:13 GMT

Hello,

I am buildinga report for a sports organisation. We've come up with a methodology that ranks clubs. The methodology currently covers three different facets of the clubs at the moment. Those being Future Club Prospects, Club Popularity and Club Support. Each club is ranked against each of three facets separately and then the governing body can determine a weighting for each facet. For example, Future Prospects might have a weighting of 50%, Popularity might be weighted at 30% and Support is therefore 20%. Each of the three separate rankings is multiplied by their weighting and all three are added together to generate a score. I then want to rank all clubs by their score.

The above is working well for me with the dax I have posted below. However, I'd like to modify it to only return a table that shows the Top 10 Clubs who would be deemed to be 'Thriving'. I'd also like to return a second table that only shows the Bottom 10 Clubs who would be deemed to be 'At Risk'.

I am struggling to return the two separate tables I need above. Can someone please assist?

This is my dax to determine each club's score:

Score =

var Score =

([4Yr Player Pool Share Rankings] * SELECTEDVALUE('Share of Playing Pool Weighting'[Selected Weighting])) +

([4Yr Youth & Senior Growth Rankings] * SELECTEDVALUE('Share of Youth & Senior Growth Weighting'[Selected Weighting])) +

([4Yr Weighted Player to Volunteer Rankings] * SELECTEDVALUE('Share of Player to Volunteer Ratio Weighting'[Selected Weighting]))

return

Score

This is my dax to then rank each club based on their score:

Rank = RANKX( ALL(Clubs[Club Name] ), CALCULATE([Score]),,DESC)

Many thanks in advance!

Re: Returning Top and Bottom 10 Ranked Items

Jihwan_Kim — Tue, 24 Jan 2023 08:28:16 GMT

Hi,

I assume [Score] is a measure and you want to create table visualization.

Please try to write a measure something like below.

Top 10 rank measure: = CALCULATE ( [Score], KEEPFILTERS ( TOPN ( 10, ALL ( Clubs[Club Name] ), [Score], DESC ) ) )

Re: Returning Top and Bottom 10 Ranked Items

RNZALR — Tue, 24 Jan 2023 09:27:13 GMT

Hi Jihwan,

That is very nearly the answer I think. I am getting a few issues though:

The value being returned appears to be a score rather than a ranking number. I'd like it to be a rank as this is easily understanding to the user.
All clubs after the top 10 continue to appear and have been given a score of 1. I'd like them to vanish completely so that the list only returns 10. I know I could filter them out in the filter pane by removing values that = 1 but I presume there is a better way.
The list returned differs in order from the list I have already got set up as a heat map. Below is a screen shot of what I am getting. I have a slicer on the page and have set it to the 2022 season. My matrix ranks all clubs from 1 down to the very last club. This is done using the Club names in the Rows and the dax measure called Rank that I shared in my original post. On top and to the right I've put a table using the Club names again but this time the measure is the dax you gave me above called Top 10 Rank. You'll note:
1. The clubs in the top 10 in both lists are the same
2. The order in which they appear in each list is different. I would like them to be in the same order and I'd preferably like the ranking shown rather than a score which to be honest, I don't really recognise.

Your thoughts would be greatly appreciated!

Re: Returning Top and Bottom 10 Ranked Items

Jihwan_Kim — Tue, 24 Jan 2023 09:39:45 GMT

Hi,

thank you very much for your message.

If it is OK with you, please share your sample pbix file's link (onedrive, googledrive, dropbox, others), and then I can try to come up with a more accurate solution that meets your requirement.

Thank you.