Forum Discussion

sublog's avatar
sublog
Icon for Helper III rankHelper III
6 years ago
Solved

RANKX woes

Need some help here. I have this formula that I am using to rank each location in my locations dimension table. RankX would well using this formula, but when I use the arrow buttons to go from location to subregion to region, the rank no longer works as expected. 

 

Here's my formula. 

Dilution Rank (rev) = RANKX(ALLSELECTED('Branches'),[Cancel Rate % (rev)],,ASC)
 
In my current by location look it has 25 individual locations. When I aggregate those up to the next subregion level I only have 9 locations. My ranking does not rank them 1-9 as I would expect. 
 
I do have a complicated model and I can't share the data as it's too sensitive/large anyways. Just looking to understand why it breaks when I roll it up. 
 

 

 

Need Help
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    Hi,

     

    According to your description, i create a table to test:

    Then i create a measure to calculate each level's rank:

    Rank = 
IF (
    ISINSCOPE ( Pupolation[Address] ),
    RANKX (
        CALCULATETABLE(DISTINCT(Pupolation[Address]),ALLSELECTED(Pupolation)),
        CALCULATE ( SUM ( Pupolation[Population] ) ),
        ,
        ASC
    ),
    IF (
        ISINSCOPE ( Pupolation[Location] ),
        RANKX (
            CALCULATETABLE(DISTINCT(Pupolation[Location]),ALLSELECTED(Pupolation)),
            CALCULATE ( SUM ( Pupolation[Population] ) ),
            ,
            ASC
        ),
        RANKX (
            CALCULATETABLE(DISTINCT(Pupolation[Country]),ALLSELECTED(Pupolation)),
            CALCULATE ( SUM ( Pupolation[Population] ) ),
            ,
            ASC
        )
    )
)

    And it shows:

     

    Hope this helps.

     

    Best Regards,

    Giotto Zhi

     

     

2 Replies