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

 

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

That works great, thank you so much Cherie :)