Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |