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

mattbrice's avatar
mattbrice
Solution Sage
8 years ago

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's avatar
    cs_rob
    Frequent Visitor
    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
        cs_rob
        Frequent Visitor
        8 years ago

        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

    • mattbrice's avatar
      mattbrice
      Solution Sage
      8 years ago

      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'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.