Forum Discussion
Solved
RANKX Returning 1 for every value
I have a simple table ('Population 2017') with population numbers ('Population 2017'[Population] ) by ward 'Population 2017'[Ward Name] in the GB. It totals 2.2m rows of data. I want to add a rank whe...
Hi TomNorth
Try this:
RankingMeasure = IF ( ISFILTERED ( 'Population 2017'[County] ), RANKX ( ALLSELECTED ( 'Population 2017' ), CALCULATE ( SUM ( 'Population 2017'[Population] ) ), , DESC ), RANKX ( ALL ( 'Population 2017'[Ward Name] ), CALCULATE ( SUM ( 'Population 2017'[Population] ) ), , DESC ) )Regards,
Cherie
AlB
Community Champion
Community ChampionHi TomNorth
Try this for the ranking calculated column:
RankingColumn = RANKX ( 'Population 2017', 'Population 2017'[Population], DESC )
Thank you @AIB. That's really helpful and adds a rank column that works correctly. Is there a way to add this as a measure though? I have some slicers on my report, e.g. Male/ Female. The Calculated Column works great for All, but when a slicer is applied I would like the Rank to update e.g. the Number 1 rank based on the currently filtered context. Thank you, Tom
- AlB
Please give me an example of how the measure will be used. What rows on the matrix you'll be using, what fields on the slicers, etc. I want to get a better idea of what you are attempting before trying to answer.
It would also help if you show the table you have with its columns
- Thank you @AIB In my 'Population 2017' table I have the following columns: [County], [Ward Name], [Gender], [Population] and [RankingColumn] (just added from your previous post.) The aim is to have a matrix table that shows [Ward Name], [Population] and Rank. The report will have 2 Slicers. One on County and one on Gender. All Ward's fit into a County so applying that filter just narrows the Ward list down to a geographic area. I would like the Rank to work dynamically so when you apply a slicer, the rank amends. Thank you, Tom
- AlB
If you are to use it in that way, eleiminate the calculated clumn we created earlier and use this mesure instead:
RankingMeasure = RANKX ( CALCULATETABLE ( Table1; ALL ( Table1[Ward]; Table1[Population] ) ); Table1[Population]; SELECTEDVALUE ( Table1[Population] ); DESC )