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
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
Community Champion
Community Champion
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 )