Forum Discussion

cs_rob's avatar
cs_rob
Frequent Visitor
8 years ago
Solved

Rank based on aggregate

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 wan...
Need Help
  • cs_rob's avatar
    cs_rob
    8 years ago

    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

cs_rob's avatar
cs_rob
Frequent Visitor
8 years ago

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's avatar
mattbrice
Solution Sage
8 years ago

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.