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 

 

I am not sure if I can have a data sort table in the same DataTbl model. I have created a new GeoSortOrder model to sort my region. Can you guide me on how to sort my data?

 

If you have guided me with the outcome, it will be more supportive.

 

I am attaching pbix file for your reference.

 

https://spaces.hightail.com/receive/BzqyRCwQmP 

danextian's avatar
danextian
Super User
2 years ago

Hi Anonymous ,

You didn't use the the column from the separate Geo table. Custom sorts do not flow from a table to another.  Also, you need to reference the column used in the visual in RANKX or you will either not see   a rank or the result is incorrect.