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

[Anonymous]

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 its closer to what I want, but it's not what I want. 

I read documentation (I think) on RANKX and I see that it "Returns the rank of an expression evaluated in the current context in the list of values for the expression evaluated for each row in the specified table.", which is a riddle if there ever was one, and the syntax is:

RANKX(<Table>, <Expression> [, <Value>] [, <Order>] [, <Ties>]).

The issue is the confusion caused by the fact that this is a RANKX with a nested RANKX() + DIVIDE() as the expression, so I'm lost trying to get a top 5, sorting by A descending, then B ascending. 

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

I got:

I was trying to get Descending by "Money Left on the Table", then Ascending by "Care Gaps to Potential". How would I do that?

Can you help me put that 'ASC' in the right place @amitchandak?

Thank you,

Mo

[v-yalanwu-msft · Accepted solution]

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.

[v-yalanwu-msft · Accepted solution]

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 · Accepted solution]

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

[Anonymous]

@amitchandak 

Now I have this:

Could the manual filter over "Measure" be disables so we can be sure it is not interfereing with the DAX calculation. Care Gaps.... should be ascending within one value of Money....

Thank you, 

Mo

[amitchandak]

@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
)