Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
cs_rob
Frequent Visitor

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 FY 2017.png

 

 

TABLE USED FOR AUG-17: RANK IS 4 RATHER THAN 3Table Aug 17.png

 

 

Kind regards,

 

cs_rob

1 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

View solution in original post

7 REPLIES 7
mattbrice
Solution Sage
Solution Sage

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

 

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.