topic Re: Getting RANKX to rank parent only in matrix in DAX Commands and Tips

Getting RANKX to rank parent only in matrix

Moonc — Thu, 24 Aug 2023 00:45:53 GMT

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?

Re: Getting RANKX to rank parent only in matrix

Moonc — Thu, 24 Aug 2023 01:51:50 GMT

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.

Re: Getting RANKX to rank parent only in matrix

Anonymous — Mon, 28 Aug 2023 08:32:06 GMT

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.