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

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

7 Replies

  • 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