Forum Discussion

SupaJ68's avatar
SupaJ68
New Member
7 years ago
Solved

Rank

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 a...
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    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's avatar
    Zubair_Muhammad
    7 years ago

    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
)

Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
7 years ago

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's avatar
    Zubair_Muhammad
    Community Champion
    7 years ago

    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's avatar
      SupaJ68
      New Member
      7 years ago

      This works perfectly.  Thank you!!! :smileyvery-happy: