RANKX Returning 1 for every value

[TomNorth]

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 where the highest population ward has 1, the second 2 and so on down to 2.2m. I have created a measure called Total Population = SUM('Population 2017'[Population]) I have then tried to calculate the rank based on this measure: Population Rank = RANKX (ALL ('Population 2017'[Population]'), [Total Population], , DESC) However, when I create a visualisation the Population Rank column is showing a 1 for every ward? Any help / what I am doing wrong gratefully received. Thank you in advance.

[v-cherch-msft]

Hi  @TomNorth

You may refer to below measure with ALLSELECTED Function. Attached the simplified sample file.Here is the article about RANKX measure for your reference.

Measure =
IF (
    ISFILTERED ( 'Population 2017'[County] )
        || ISFILTERED ( 'Population 2017'[Gender] ),
    RANKX (
        ALLSELECTED ( 'Population 2017' ),
        CALCULATE ( SUM ( 'Population 2017'[Population] ) ),
        ,
        DESC
    ),
    RANKX (
        ALL ( 'Population 2017'[Ward Name] ),
        CALCULATE ( SUM ( 'Population 2017'[Population] ) ),
        ,
        DESC
    )
)

Regards,

Cherie

[TomNorth]

Hi @v-cherch-msft

Thank you for your reply. This nearly works so thank you.

With no slicers applied, I have a rank on wards which is great!

With just county slicers applies, its reducing the wards just to that county with dynamic ranking which again is great!

However, when I click on the Gender slicer, (All, Male or Female) the ranking is reverting back to 1's for every ward.

I try applying the gender slicer on its own e.g. just Female without the county slicer on, and the same thing happens that all ranks show as 1. Not sure if you know what this is? I am using this code, the same as your post:

Measure =
IF (
    ISFILTERED ( 'Population 2017'[County] ) 
         || ISFILTERED ( 'Population 2017'[Gender] ),     
    RANKX (
        ALLSELECTED ( 'Population 2017' ),
        CALCULATE ( SUM ( 'Population 2017'[Population] ) ),
        ,
        DESC
    ),
    RANKX (
        ALL ( 'Population 2017'[Ward Name] ),
        CALCULATE ( SUM ( 'Population 2017'[Population] ) ),
        ,
        DESC
    )
)

[v-cherch-msft]

Hi @TomNorth

Could you share your sample data which could reproduce your scenario?The measure works for my sample data and i don't know what's the difference between your and my data.You can also upload the pbix file to OneDrive and post the link here. Do mask sensitive data before uploading.

Regards,

Cherie

[AlB]

@TomNorth

Did what I suggested not work?

[TomNorth]

Hi @AlB

The values all were the max value. I have added to the file I just uploaded as RankingMeasure2.

Thank you for your help 🙂

[v-cherch-msft]

HIi @TomNorth

I cannot access the file. Could you share it via onedrive instead of sharepoint?

Regards,

Cherie

[TomNorth]

Hi Cherie,

Sorry! Wrong link - try this one 🙂

RANK Test Power BI File

Thank you,

Tom

[v-cherch-msft · Accepted solution]

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

[TomNorth]

That works great, thank you so much Cherie 🙂

[themistoklis]

@TomNorth

Change your formula to this one. Use a Measure and not a Column:

Population Rank  = 
IF (
    HASONEVALUE ( 'Population 2017'[Ward Name] ), 
    RANKX ( ALL ( 'Population 2017'[Ward Name] ), [Total Population] ,,DESC)
)

[AlB]

Hi @TomNorth

Try this for the ranking calculated column:

RankingColumn =
RANKX ( 'Population 2017', 'Population 2017'[Population], DESC )

[TomNorth]

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]

@TomNorth

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

[TomNorth]

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]

@TomNorth

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
)