Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Rank calculation

Hello,   I am trying to calculate rank across regions (excluding total region to get region rank) and specifically to a Product category. My formula is showing me the same rank across all regions a...
Need Help
Show and Tell
  • danextian's avatar
    danextian
    2 years ago

    hI Anonymous ,

     

    Since there is no filter modifier on Geography, RANKX is evaluated for each distinct value in Geographyinstead against all distinct values.

    Rank2 =
VAR __REGIONS = { "East Region", "West Region", "North Region", "Central Region" }
RETURN
    IF (
        SELECTEDVALUE ( DataTbl[Geography] )
            IN __REGIONS --rank will still show for US so a conditional statement needs to handle this. 
                && ISINSCOPE ( DataTbl[Geography] ),
        --to remove rank from the total
        RANKX (
            FILTER ( ALL ( DataTbl[Geography] ), DataTbl[Geography] IN __REGIONS ),
            CALCULATE ( [ShrChgProA], KEEPFILTERS ( DataTbl[Product] = "Prod A" ) )
        )
    )

     

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

danextian  Can you help me in the custom sorting region? As of now, it is appearing in Alphabetical order. I

am trying to get results as below. I have created new sort table and than sort by region order. But by doing this my rank is not populating. Can you guide me?

 

  • danextian's avatar
    danextian
    Super User
    2 years ago

    Hi Anonymous ,

    That is the expected behaviour of RANKX if applied to a column that's been sorted by another column. You can just include the sort column in your RANKX table 

     ALL ( DataTbl[Geography], DataTbl[Sort] )
    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      danextianI am unable to connect how DataTbl[Sort] can understand the sequence I am looking for? Can you show me output?

      • danextian's avatar
        danextian
        Super User
        2 years ago

        Here is the complete measure.  Replace sort with the correct column.  If this doesn't work, please post your updated pbix.

         

        Rank2 =
VAR __REGIONS = { "East Region", "West Region", "North Region", "Central Region" }
RETURN
    IF (
        SELECTEDVALUE ( DataTbl[Geography] )
            IN __REGIONS --rank will still show for US so a conditional statement needs to handle this. 
                && ISINSCOPE ( DataTbl[Geography] ),
        --to remove rank from the total
        RANKX (
            FILTER ( ALL ( DataTbl[Geography], DataTbl[Sort] ), DataTbl[Geography] IN __REGIONS ),
            CALCULATE ( [ShrChgProA], KEEPFILTERS ( DataTbl[Product] = "Prod A" ) )
        )
    )