Keep RANKX values static

[Anonymous]

In my organization we have our stores set up in a hierarchy that goes Region > District > Store. Each level of hierarchy has its own performance scorecard, and each Store/District/Region is ranked based on that score. I currently use this formula to produce a rank at each level:

SC Rank = IF(

ISFILTERED(Roster[Store]), RANKX(ALL(Roster[Store]),[SC Total Score]), IF(

ISFILTERED(Roster[District]), RANKX(ALL(Roster[District]),[SC Total Score]), IF(

ISFILTERED(Roster[Region]), RANKX(ALL(Roster[Region]),[SC Total Score]))))

This works as I'd expect it to at each level UNTIL a filter is applied. If someone selects to just view a specific District all Stores in that District are re-ranked against just each other rather than still showing their global rank. Is there a way to make it so that BI "remembers" what their global rank is (leave the rank static) rather than globally changing it?

[amitchandak]

@Anonymous , Create a measure rank with allselected in place of ALL

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415

[lbendlin · Accepted solution]

Use a calculated column rather than a measure.  Calculated columns are only refreshed once during data source refresh, and are unaffected by filter changes.