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 and US Region data is also populating which shouldn't't be populating.

 

Can you help me identify issue in my DAX code.

Below is my code.

 
ShrChgProA = SUM(DataTbl[Shr Chg Prod A])

 

RankExcludingUSRegionProdA =
VAR SelectedData =
    FILTER(
        'DataTbl',
        'DataTbl'[Geography] IN {"East Region", "West Region", "North Region", "Central Region"} &&
        'DataTbl'[Product] = "Prod A"
    )
RETURN
    RANKX(
        SelectedData,
        [ShrChgProA],
        ,
        DESC,
        Dense
    )
 
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" ) )
        )
    )

     

     

7 Replies

  • danextian's avatar
    danextian
    Super User
    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?