Fixed versus Dynamic Rank with RankX

[Anonymous]

I have a basic matrix table with Product Name, Profit and two rank columns (1 fixed, 1 dynamic) that are calculated with the following measures:

(1) Product Profit Rank = RANKX(ALLSELECTED(Orders[Product Name]),CALCULATE(SUM(Orders[Profit])))

**This measure is dynamic, so it adjusts to the filters I have added. 

(2) Product Profit Fixed Rank = RANKX(ALL(Orders[Product Name]),CALCULATE(SUM(Orders[Profit])))

** I used the ALL function - rather than ALLSELECTED like in (1) - becuase I want it to be fixed. So as I change the filter, or click on another visualization, I want the dyanmic formula to readjust the #1 rank, but I want the rankings for the second measure to stay fixed. So there may be a region where product X is #1 rank, but the fixed rank compared to the entire dataset is #10. Why is my second formula (2) not fixed as filter context changes? it keeps dynamically updating like (1).

Thanks,

Frazer

[amitchandak]

@Anonymous , you rank all product[name] will be dynamic inside any group by/axis/legend you will use. This how it works. Anything else you add to visual, will create a group for Rank

example

City Rank = RANKX(all(Geography[City]),[Sales]) // this gives me rank 1only when I use with City ID
Geography Rank = RANKX(all(Geography),[Sales]) // correct Rank with city id as both city and city Id at lowest level
City and ID Rank = RANKX(all(Geography[City],Geography[City Id]),[Sales]) // correct Rank with city id as both city and city Id are part of the Rank.

See if this can help you a bit.

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

[Anonymous]

@amitchandak 

Sorry, I dont follow what you are trying to say. Isn't the point of using ALL so that the expression will ignore filter context? 

ALL - "Returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied. This function is useful for clearing filters and creating calculations on all the rows in a table."

So if I want to create a universal rank that stays the same is there a way to adjust my current formula to do that?

https://drive.google.com/file/d/1eqGAh4aieAVV9LgsM1heHP62Ey65Usu4/view?usp=sharing

Product Profit Fixed Rank = RANKX(ALL(Orders[Product Name]),CALCULATE(SUM(Orders[Profit])))

Thanks

[Anonymous · Accepted solution]

// This is a global rank that
// does not respond to
// any filters.

[Product Profit Fixed Rank] = 
    RANKX(
        ALL( Orders[Product Name] )
        CALCULATE(
            SUM( Orders[Profit] ),
            ALLEXCEPT(
                Orders,
                Orders[Product Name]
            )
        )
    )

[Anonymous]

Yep, works perfectly. Exactly what I needed as well. The requirement description is very clear and this proposed solution works well. I still don't understand why the ALL() function is not enough to remove all the filters, wether in the visual itself as well as filters in slicers and in the filters pane, but the solution works. Thanks so much.

[Anonymous]

Amazing. Exactly what I was looking for. Thanks!

[Greg_Deckler]

@Anonymous - Try:

Product Profit Fixed Rank = RANKX(ALL(Orders),CALCULATE(SUM(Orders[Profit])))

Otherwise, Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Anonymous]

@Greg_Deckler Thanks for the information on better communicating my questions. This time around I have attached a link to a sample file of the question I am trying to solve. Essentially, I am trying to make the Fixed rank column, well... fixed, so as I click on different categories in the stacked column chart the dynamic rank will adjust 1,2,3,4,5, etc, while the fixed will stay the same consistent with the global ranking 3,5,7,9,etc,.  

Link: https://drive.google.com/file/d/1eqGAh4aieAVV9LgsM1heHP62Ey65Usu4/view?usp=sharing