Forum Discussion
Fixed versus Dynamic Rank with RankX
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
7 Replies
- Greg_Deckler
Community Champion
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.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
- amitchandak
Super User
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/367415Sorry, 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
