RANKX in matrix table

[aleivar]

Hi All,

I am trying to use single RANKX formula in a matrix table so that the rank is calculated correctly when I drill down.

The image below shows the correct rank for LP

But when I drill down, the rank is not calculated correctly. The Rank Total Sales (Brand) shows the correct rank

I tried the following RANK formula but it's not calculating correctly

Rank Total Sales = 
VAR LPGROUPRANK =
RANKX(
    ALL(
        'OCS-daily-2018-2023'[lp_group]
    ),
    [Total Sales],
    ,
    DESC,
    Dense
)
VAR BRANDRANK =
RANKX(
    ALLSELECTED(
        'OCS-daily-2018-2023'[brand]
    ),
    [Total Sales],
    ,
    DESC,
    Dense
)
RETURN
SWITCH(
    TRUE(),
    ISINSCOPE('OCS-daily-2018-2023'[lp_group]
    ),
    LPGROUPRANK,
    ISINSCOPE(
        'OCS-daily-2018-2023'[brand]
    ),
    BRANDRANK
)

My model looks like this:

Any help is greatly appreciated

[v-xinruzhu-msft · Accepted solution]

Hi @aleivar 

You can try the following measure

Rank Total Sales =
VAR LPGROUPRANK =
    RANKX (
        ALLSELECTED ( 'OCS-daily-2018-2023'[lp_group] ),
        [Total Sales],
        ,
        DESC,
        DENSE
    )
VAR BRANDRANK =
    RANKX (
        SUMMARIZE (
            FILTER (
                ALLSELECTED ( 'OCS-daily-2018-2023' ),
                [lp_group] = SELECTEDVALUE ( 'OCS-daily-2018-2023'[lp_group] )
            ),
            [brand]
        ),
        [Total Sales],
        ,
        DESC,
        DENSE
    )
RETURN
    IF ( ISINSCOPE ( 'OCS-daily-2018-2023'[brand] ), BRANDRANK, LPGROUPRANK )

Best Regards!

Yolo Zhu

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