Forum Discussion

Moonc's avatar
Moonc
Frequent Visitor
3 years ago
Solved

Getting RANKX to rank parent only in matrix

I have a matrix with a grouping and a subgrouping and then a measure in values that is summing. I have a measure that ranks both the Grouping and the Subgrouping independently of each other

Example: https://imgur.com/crq9cxw

I have this logic working fine, no issues

I want to figure out how to give the rank of the top level group to the values in the sub group. It seems like whenever I rank just the top level group, the ranks for the subgroups just repeat 1.

Example: https://imgur.com/MCyMCiZ

Any ideas?

  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Moonc ,

     

    Here I create a sample to have a test and I hope it could help you.

    Measure:

    Measure = CALCULATE(SUM('Table'[Value]))
    Rank = 
VAR _SUMMARIZE =
    SUMMARIZE (
        ALL ( 'Table' ),
        'Table'[Group],
        "Sum by Group", CALCULATE ( SUM ( 'Table'[Value] ), ALLEXCEPT ( 'Table', 'Table'[Group] ) )
    )
VAR _RANK =
    ADDCOLUMNS (
        _SUMMARIZE,
        "Rank", RANKX ( _SUMMARIZE, [Sum by Group],, ASC, DENSE )
    )
RETURN
    MAXX ( FILTER ( _RANK, [Group] = MAX ( 'Table'[Group] ) ), [Rank] )

    Result is as below.

     

    Best Regards,

    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • Moonc's avatar
    Moonc
    Frequent Visitor
    3 years ago

    Added clarity. If I just do a RANKX for the main grouping (A/B/C) and put it in the matrix, I get this: https://imgur.com/pDYfFDD.

     

    In this scenario, I want the 1s to be the rank of their parent.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi Moonc ,

       

      Here I create a sample to have a test and I hope it could help you.

      Measure:

      Measure = CALCULATE(SUM('Table'[Value]))
      Rank = 
VAR _SUMMARIZE =
    SUMMARIZE (
        ALL ( 'Table' ),
        'Table'[Group],
        "Sum by Group", CALCULATE ( SUM ( 'Table'[Value] ), ALLEXCEPT ( 'Table', 'Table'[Group] ) )
    )
VAR _RANK =
    ADDCOLUMNS (
        _SUMMARIZE,
        "Rank", RANKX ( _SUMMARIZE, [Sum by Group],, ASC, DENSE )
    )
RETURN
    MAXX ( FILTER ( _RANK, [Group] = MAX ( 'Table'[Group] ) ), [Rank] )

      Result is as below.

       

      Best Regards,

      Rico Zhou

       

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.