Forum Discussion
Rank decimal values using DAX
Is there a way to rank decimal values using DAX?
I'm trying to rank the decimal values of the measure 'Random values' but i'm not getting the rank correctly.
Measure used:
RANKX is hard. I'm pretty good with DAX but rarely get it right on the first try.
Standard RANKX:
RANKX ( ALL ( Table1[Name] ), [Random values],, ASC)The key here is iterating over distinct names rather than all of the rows of Table1.
Greg_Deckler's suggested approach is more like this:
Rank of random values = VAR CurrValue = [Random Values] RETURN COUNTROWS ( FILTER ( SUMMARIZE ( ALL ( Table1 ), Table1[Name], "Value", [Random Values] ), [Value] <= CurrValue ) )To me, this is a bit more intuitive than either of those above:
Rank of random values = RANKX ( SUMMARIZE ( ALL ( Table1 ), Table1[Name] ), [Random Values], , ASC )Anonymous I just mocked this up because I was curious. I'm wondering if the problem lies in your [Random value] measure? Because when I put the information above in a table as columns I got the right answer:
Measure Rank of random values = RANKX( ALL(Table5),CALCULATE(SUM([Random values])),,ASC)
5 Replies
Anonymous Try doing it without RANKX: (1) To *Bleep* with RANKX! - Microsoft Power BI Community
RANKX is hard. I'm pretty good with DAX but rarely get it right on the first try.
Standard RANKX:
RANKX ( ALL ( Table1[Name] ), [Random values],, ASC)The key here is iterating over distinct names rather than all of the rows of Table1.
Greg_Deckler's suggested approach is more like this:
Rank of random values = VAR CurrValue = [Random Values] RETURN COUNTROWS ( FILTER ( SUMMARIZE ( ALL ( Table1 ), Table1[Name], "Value", [Random Values] ), [Value] <= CurrValue ) )To me, this is a bit more intuitive than either of those above:
Rank of random values = RANKX ( SUMMARIZE ( ALL ( Table1 ), Table1[Name] ), [Random Values], , ASC )Anonymous I just mocked this up because I was curious. I'm wondering if the problem lies in your [Random value] measure? Because when I put the information above in a table as columns I got the right answer:
Measure Rank of random values = RANKX( ALL(Table5),CALCULATE(SUM([Random values])),,ASC)Did you mock it up where the table had more than one row per Name? I'm assuming [Random values] is an aggregate over multiple rows rather than a single value.
AlexisOlson Hmm, good point, I just had single rows for each Name. It's so hard to know exactly how someone's data is setup! But, it definitely isn't that RANKX can't handle ranking decimal numbers, that's what I was curious about.