Forum Discussion

TomNorth's avatar
TomNorth
Frequent Visitor
7 years ago
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 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.
Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    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

15 Replies

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Hi TomNorth

     

    Try this for the ranking calculated column:

     

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

     

    • TomNorth's avatar
      TomNorth
      Frequent Visitor
      7 years ago
      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's avatar
        AlB
        Icon for Community Champion rankCommunity Champion
        7 years ago

        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

  • themistoklis's avatar
    themistoklis
    Icon for Community Champion rankCommunity Champion
    7 years ago

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

     

  • v-cherch-msft's avatar
    v-cherch-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    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's avatar
      TomNorth
      Frequent Visitor
      7 years ago

      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's avatar
        v-cherch-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        7 years ago

        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