Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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 November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.