Getting RANKX to rank parent only in matrix

[Moonc]

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?

[Moonc]

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 · Accepted solution]

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.