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" ) )
        )
    )

     

     

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" ) )
        )
    )