RANKX, using a second value to break tiebreaker (TOP N returns additional rows)

[Anonymous]

After applying the TOP N filter to a table visual im left with an extra row due to a tie breaker, therefore i'm looking to create a RANKX measure using an additional value to break this tie breaker when a tie occurs.

Since there is a tie when using count i would like to break the tie by using the 'Amount' Column which would place the row with 28 in the first rank, and 24 displaying Rank 2

Thank you to everyone in advance for the help!

[v-yingjl · Accepted solution]

Hi @Anonymous ,

You can create this measure to calculate:

Rank =
VAR a =
    CALCULATETABLE (
        FILTER (
            DISTINCT ( 'Table'[Food] ),
            RANKX (
                ALLSELECTED ( 'Table' ),
                CALCULATE ( COUNT ( 'Table'[Heath Rating] ) ),
                ,
                DESC,
                DENSE
            ) = 1
        ),
        ALLSELECTED ()
    )
RETURN
    IF (
        CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Food] IN a ) = 0,
        BLANK (),
        RANKX (
            GROUPBY (
                FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Food] IN a ),
                'Table'[Heath Rating]
            ),
            CALCULATE ( SUM ( 'Table'[Amount] ), 'Table'[Food] IN a ),
            ,
            DESC,
            DENSE
        )
    )

Attached a sample file in the below, hopes it could help.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mahoneypat]

Please see this article/video.

RANKX on multiple columns with DAX and Power BI - SQLBI

Pat