topic Re: Fixed versus Dynamic Rank with RankX in DAX Commands and Tips

Fixed versus Dynamic Rank with RankX

Anonymous — Wed, 02 Sep 2020 23:08:43 GMT

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

Re: Fixed versus Dynamic Rank with RankX

Greg_Deckler — Thu, 03 Sep 2020 00:34:53 GMT

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.

Re: Fixed versus Dynamic Rank with RankX

Anonymous — Thu, 03 Sep 2020 01:18:05 GMT

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

Re: Fixed versus Dynamic Rank with RankX

amitchandak — Thu, 03 Sep 2020 03:00:21 GMT

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

Re: Fixed versus Dynamic Rank with RankX

Anonymous — Thu, 03 Sep 2020 03:22:04 GMT

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

Re: Fixed versus Dynamic Rank with RankX

Anonymous — Thu, 03 Sep 2020 10:09:52 GMT

// 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] ) ) )

Re: Fixed versus Dynamic Rank with RankX

Anonymous — Thu, 03 Sep 2020 15:22:36 GMT

Amazing. Exactly what I was looking for. Thanks!

Re: Fixed versus Dynamic Rank with RankX

Anonymous — Thu, 10 Aug 2023 01:02:13 GMT

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.