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.
Hi all,
First of all, apologies for a very "noobish" question, but the silver lining is that this this should be a pretty easy thumbs up.
Is there a way to rank based on aggregates?
I want to select the top values based on the aggregates for selected filters:
Measure Amount = CALCULATE(
SUM('Sample'[Sales_Amount]),
ALLSELECTED('Sample'[Month_Key])
)
However I'm having trouble with the ranking portion of this. I thought it would have been a simple manner of applying the above to the RANKX function:
Rank = RANKX (
ALLSELECTED('Sample'[Product]),
CALCULATE(
SUM('Sample'[Sales_Amount]),
ALLSELECTED('Sample'[Month_Key])
)
)
But this is causing issues where negative aggregates appear, as it appears to be keeping a placeholder for intermediate values?
This error is obvious due to the fact that ranking works for all months except August, screenshot below, which includes a negative value:
TABLE USED FOR FULL YEAR 2017 - RANKING IS AS EXPECTED:
TABLE USED FOR AUG-17: RANK IS 4 RATHER THAN 3
Kind regards,
cs_rob
Solved! Go to Solution.
Thanks Matt,
I tried that and it didn't work. I've only just started learning DAX, but I thought that using VALUES would have then given me only the distinct values regardless?
I tried another workaround which seems to work (I've used 2 measures purely to split the code):
Measure Amount = CALCULATE(
SUM('Sample'[Sales_Amount]),
ALLSELECTED('Sample'[Month_Key])
)
Rank = RANKX (
FILTER(ALLSELECTED('Sample'[Product]), [Measure Amount] <> 0),
[Measure Amount]
)
Thanks again for your help with this
Your problem is that the ‘Rank’ measure, even though not displaying, is calculating a value (0) for ‘Clothing’ and including in ranking because of the first ALLSELECTED. Change it to VALUES( ‘Sample’[Product] ) and it should work.
Thanks Matt,
I tried that and it didn't work. I've only just started learning DAX, but I thought that using VALUES would have then given me only the distinct values regardless?
I tried another workaround which seems to work (I've used 2 measures purely to split the code):
Measure Amount = CALCULATE(
SUM('Sample'[Sales_Amount]),
ALLSELECTED('Sample'[Month_Key])
)
Rank = RANKX (
FILTER(ALLSELECTED('Sample'[Product]), [Measure Amount] <> 0),
[Measure Amount]
)
Thanks again for your help with this
Well...I don't know what I was thinking as you are right my suggestion won't work. Looking and thinking for 1/2 a second I realize VALUES will return the current visual row value for 'Sample'[Product] and therefore rank all visual rows as a 1.
In fact I must confess I played around with this for a while today and couldn't quite get it to work. I mean, I understand why you are getting the results you are...but when I tried a few different versions I couldn't get it quite right. Marco's article isn't really on point in this case either from what I see.
So if someone else has a way to solve this 100%, I'll check back.
Sorry for bad advice.
Thanks Matt,
Did you check my proposed solution above? It seems to be producing the results I was after.
If so, I'll mark this as solved so anyone encountering the same issue has a workaround.
I did look at your solution and if it works for you that's great. Only part I don't like is that since you have potential of negative values, then I would assume zero would also be a potential result. But since your measure excludes zero values, they wouldn't get ranked. Personally I would prefer if there was a more elegant solution. I'll continue to play aournd with it and see if I come up with a new approach.
Hi @cs_rob,
I 'd like to suggest you take a look at marco's blog which also told about how to use rankx fucntion with a measure:
Use of RANKX in Power BI measures
Regards,
Xiaoxin Sheng
Thanks for sharing this article Xiaoxin,
This definitely enhanced my knowledge a bit and made me wary of the potential pitfalls.
Kind regards,
cs_rob
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 |
---|---|
106 | |
98 | |
75 | |
63 | |
62 |
User | Count |
---|---|
135 | |
105 | |
104 | |
80 | |
65 |