Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am trying to create a rank measure within an specific categories but ignore others. I have seen a lot of similar questions asked but couldn't find one that did exactly what I needed.
Sample Data
Order Status | Order ID | Account ID | Order $ | Rank (Desired) |
Delivered | aaa | Cust A | 100 | 1 |
Delivered | bbb | Cust B | 50 | 2 |
Delivered | ccc | Cust A | 30 | 3 |
Shipped | ddd | Cust C | 75 | 1 |
Shipped | eee | Cust D | 40 | 2 |
Received | fff | Cust B | 200 | 1 |
Received | ggg | Cust E | 15 | 2 |
Received | hhh | Cust A | 10 | 3 |
My current formula is:
Solved! Go to Solution.
@shep123
Use the following measure to calculate the rank as desired:
Rank =
RANKX(
ALLEXCEPT(Table,Table[Order Status]),
CALCULATE(SUM(Table[Order $]))
)
________________________
If my answer was helpful, please click Accept it as the solution to help other members find it useful
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hello,
Could you please guide me on this issue.
I tried the same way of creating the measure and the measure will work when you have one category however as soon as you add another category (sub category) the ranking changes.
Is there any way to define global ranking on a category?
Looking forward to your response 🙂
Thank you!
Hi all, I have a similar issue only the formula presented does not solve it. Let me explain my situation, hopefully you are able to help me out.
I have a table like this (much bigger table actually but as an example)
Table 1
Customer Country Country Group Sales
ABC Netherlands Benelux 100
ABC Belgium Benelux 50
DEF Netherlands Benelux 200
GHI Netherlands Benelux 75
JKL Belgium Benelux 20
and I was using the formula to rank them:Customer Rank TO = RANKX(ALL('table1'[Customer]), CALCULATE(SUM('table1'[Sales]),allexcept('table1','table1'[Customer]),,DESC,Dense)
when plotting this in a matrix, it works fine:
Customer Sales Customer Rank TO
DEF 200 1
ABC 150 2
GHI 75 3
JKL 20 4
And I can show the top 2 by setting a threshold of 2
[Customer Rank TO]<=2
Customer Sales Customer Rank TO
DEF 200 1
ABC 150 2
However, when I bring in country into the matrix, it does not keep the ranking on total sales by customer anymore but takes a customer-country perspective and I get more results than 2. This happens because the logic looks at the top 2 of the individual countries. JKL is in the top 2 of Belgium (but not a top 2 of Benelux). I want it to keep looking to the overall ranking and not by country. How to solve this?
I tried the formula: Customer Rank TO = RANKX(ALLexcept('table1','table1'[Customer]), CALCULATE(SUM('table1'[Sales])),,DESC,Dense) but I then don't get results (not enough memory). Any suggestion on how I can solve?
Thanks for your kind help!
Thank you in everything I tried I somehow didn't that solution
How would I adjust this formula if I wanted to be dynamic from another filter like order month for example? Based on my testing the ranks stay the same even if I filter out certain orders based on what was ordered that month.
@shep123
Use the following measure to calculate the rank as desired:
Rank =
RANKX(
ALLEXCEPT(Table,Table[Order Status]),
CALCULATE(SUM(Table[Order $]))
)
________________________
If my answer was helpful, please click Accept it as the solution to help other members find it useful
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |