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

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

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