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 whe...
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

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

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

TomNorth

 

Did what I suggested not work?

  • TomNorth's avatar
    TomNorth
    Frequent Visitor
    7 years ago

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

      HIi TomNorth

       

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

       

      Regards,

      Cherie

      • TomNorth's avatar
        TomNorth
        Frequent Visitor
        7 years ago

        Hi Cherie,

         

        Sorry! Wrong link - try this one :)

         

        RANK Test Power BI File

         

        Thank you,

         

        Tom