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 and what I am trying to Rank;

 

Any help would be greatly appreciated.

Thanks! JW

 

 

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

3 Replies

  • 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: