Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Apologies if this post seems familar, I accidently accepted a solution in my previous post but I am am still having the same issue. I have included a sample dataset that mimics the issue I am having. I have a data set that I am ranking each hierarchy in ascending order. The first level works correctly and ranks properly, the issue is that once I drill down to the next level rankx correctly ranks the subset but wants to start at the bottom of the entire population vs ranking the subset. I am attaching a sample test set that replicates my exact issue. The table has 3 columns State, Office, Amount. I created a measure: Amount_2 = CALCULATE(SUM(Amount)) so that I have a measure to rank. I then use the following Dax for my Rankx measure:
Results when expanded to office:
Results I am expecting:
Table used:
State | Office | Amount |
SC | 100 | 39 |
SC | 101 | 29 |
SC | 102 | 42 |
SC | 103 | 45 |
SC | 104 | 76 |
SC | 105 | 95 |
SC | 106 | 92 |
SC | 107 | 87 |
IL | 200 | 77 |
IL | 201 | 3 |
IL | 202 | 50 |
IL | 203 | 67 |
IL | 204 | 25 |
IL | 205 | 71 |
IL | 206 | 58 |
IL | 207 | 72 |
IL | 208 | 88 |
IL | 209 | 78 |
IN | 300 | 37 |
IN | 301 | 80 |
IN | 302 | 39 |
IN | 303 | 4 |
IN | 304 | 82 |
IN | 305 | 60 |
IN | 306 | 59 |
Solved! Go to Solution.
@JJohns As per your data,
You can use the below dax to get the ranks based on states and Office's
Rank_Office Level =
IF(ISINSCOPE(Ranking[Office]),
RANKX(
CALCULATETABLE(VALUES(Ranking[Office]),
ALLSELECTED(Ranking[Office])
),
[Total Amount]
),
IF(ISINSCOPE(Ranking[State]),
RANKX(
ALLSELECTED(Ranking[State]),
[Total Amount]
)
))
Accept the solution if it helps you.
Thanks,
Mohan V
@JJohns As per your data,
You can use the below dax to get the ranks based on states and Office's
Rank_Office Level =
IF(ISINSCOPE(Ranking[Office]),
RANKX(
CALCULATETABLE(VALUES(Ranking[Office]),
ALLSELECTED(Ranking[Office])
),
[Total Amount]
),
IF(ISINSCOPE(Ranking[State]),
RANKX(
ALLSELECTED(Ranking[State]),
[Total Amount]
)
))
Accept the solution if it helps you.
Thanks,
Mohan V
Thanks Mohan, that worked perfectly, I appreciate the help!
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
64 | |
60 | |
51 | |
36 | |
36 |
User | Count |
---|---|
81 | |
72 | |
58 | |
45 | |
44 |