Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Get Top N of a category by a value using DAX function RANKX() and a tie breaker.

I thought I was following the pattern provided in the post:  Breaking Ties in Rankings with RANKX Using Multipl... - Microsoft Power BI Community when I wrote the DAX below.  It doesn't error, and it...
Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    Hi, Anonymous ;

    Try it.

    RankedByMoney(CareGapsToPotential) =
RANKX (
    ALL ( HN_CHIP ),
    RANKX ( ALL ( HN_CHIP ), 'HN_CHIP'[Money Left on the Table],, ASC )
        * ( COUNTROWS ( 'HN_CHIP' ) + 1 )
        + RANKX ( ALL ( HN_CHIP ), 'HN_CHIP'[Care Gaps to Potential],, DESC )
)

    The final output is shown below:


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

  • Anonymous's avatar
    Anonymous
    4 years ago

    I created a rank in MS SQL Server using ROW_NUMBER() OVER(PARTITION BY ... ORDER BY...)

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

Anonymous , Check if this can help

RankedByMoney(CareGapsToPotential) =
RANKX(ALL('HN_CHIP'),
RANKX (ALL('HN_CHIP'), 'HN_CHIP'[MoneyLeftOnTable], , ASC)
+ DIVIDE(
RANKX(ALL('HN_CHIP'), 'HN_CHIP'[cgToMaxPotential],,asc),
(COUNTROWS(ALL('HN_CHIP')) + 1)
) ,,asc
)