- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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 and US Region data is also populating which shouldn't't be populating.
Can you help me identify issue in my DAX code.
Below is my code.
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

hI @Siddharth_2987 ,
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" ) )
)
)
Dane Belarmino | Microsoft MVP | Proud to be a Super User!
Did I answer your question? Mark my post as a solution!
"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

@danextian Can you help me in the custom sorting region? As of now, it is appearing in Alphabetical order. I
am trying to get results as below. I have created new sort table and than sort by region order. But by doing this my rank is not populating. Can you guide me?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi @Siddharth_2987 ,
That is the expected behaviour of RANKX if applied to a column that's been sorted by another column. You can just include the sort column in your RANKX table
ALL ( DataTbl[Geography], DataTbl[Sort] )
Dane Belarmino | Microsoft MVP | Proud to be a Super User!
Did I answer your question? Mark my post as a solution!
"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

@danextianI am unable to connect how DataTbl[Sort] can understand the sequence I am looking for? Can you show me output?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Here is the complete measure. Replace sort with the correct column. If this doesn't work, please post your updated pbix.
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[Sort] ), DataTbl[Geography] IN __REGIONS ),
CALCULATE ( [ShrChgProA], KEEPFILTERS ( DataTbl[Product] = "Prod A" ) )
)
)
Dane Belarmino | Microsoft MVP | Proud to be a Super User!
Did I answer your question? Mark my post as a solution!
"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi @Siddharth_2987 ,
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.
Dane Belarmino | Microsoft MVP | Proud to be a Super User!
Did I answer your question? Mark my post as a solution!
"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

hI @Siddharth_2987 ,
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" ) )
)
)
Dane Belarmino | Microsoft MVP | Proud to be a Super User!
Did I answer your question? Mark my post as a solution!
"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - February 2025
Check out the February 2025 Power BI update to learn about new features.

Subject | Author | Posted | |
---|---|---|---|
08-15-2024 01:17 PM | |||
12-21-2024 04:45 AM | |||
10-24-2024 11:38 AM | |||
12-06-2024 08:13 AM | |||
12-21-2024 08:32 PM |
User | Count |
---|---|
84 | |
81 | |
52 | |
37 | |
36 |
User | Count |
---|---|
104 | |
85 | |
48 | |
43 | |
41 |