Rank decimal values using DAX

[Anonymous]

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:

Rank of random values = RANKX( ALL(Table1),[Random values],,ASC)

[AlexisOlson · Accepted solution]

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
)

[Greg_Deckler · Accepted solution]

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

[AlexisOlson]

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.

[Greg_Deckler]

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

[AlexisOlson · Accepted solution]

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
)

[Greg_Deckler]

@Anonymous Try doing it without RANKX: (1) To *Bleep* with RANKX! - Microsoft Power BI Community