Rank

[SupaJ68]

Hi PBI Experts,

I am trying to rank the data within a table using two different groupings and I am struggling to get the DAX examples on the forum to work for me.  Below is an example of my data and what I am trying to Rank;

Any help would be greatly appreciated.

Thanks! JW

[Zubair_Muhammad · Accepted solution]

@SupaJ68

You can use this calculated column for Type 1

RankType1 =
RANKX (
    SUMMARIZE (
        LocationValue,
        [Location],
        "Sum", CALCULATE ( SUM ( LocationValue[Value] ) )
    ),
    [Sum],
    CALCULATE (
        SUM ( LocationValue[Value] ),
        ALLEXCEPT ( LocationValue, LocationValue[Location] )
    ),
    DESC,
    DENSE
)

[Zubair_Muhammad · Accepted solution]

@SupaJ68

You can use this calculated column for Type 1

RankType1 =
RANKX (
    SUMMARIZE (
        LocationValue,
        [Location],
        "Sum", CALCULATE ( SUM ( LocationValue[Value] ) )
    ),
    [Sum],
    CALCULATE (
        SUM ( LocationValue[Value] ),
        ALLEXCEPT ( LocationValue, LocationValue[Location] )
    ),
    DESC,
    DENSE
)

[Zubair_Muhammad · Accepted solution]

@SupaJ68

And this one for RANK type 2

See attached file as well

RankType2 =
RANKX (
    SUMMARIZE (
        LocationValue,
        [SubLocation],
        "Sum", CALCULATE ( SUM ( LocationValue[Value] ) )
    ),
    [Sum],
    CALCULATE (
        SUM ( LocationValue[Value] ),
        ALLEXCEPT ( LocationValue, LocationValue[SubLocation] )
    ),
    DESC,
    DENSE
)

[SupaJ68]

This works perfectly.  Thank you!!!