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.
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!
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 |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |