Forum Discussion

aleivar's avatar
aleivar
Frequent Visitor
3 years ago
Solved

RANKX in matrix table

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

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.