Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
116 | |
108 | |
108 | |
93 | |
70 |
User | Count |
---|---|
173 | |
135 | |
131 | |
96 | |
94 |