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

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.