Rank based on aggregate

[cs_rob]

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

[mattbrice]

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. 

[cs_rob · Accepted 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

[mattbrice]

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.

[cs_rob]

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.

[mattbrice]

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.  

[Anonymous]

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

[cs_rob]

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