RANKX columns

[Anonymous]

This is my data.  I created two columns

1) "S&P Rank Usage": ranks the accounts within a quarter by the "Numeric S&P with Median"

2) "Exp Rank by Credit Usage": ranks the "S&P Rank Usage" by exposure

What I would like to have is to see the top 25 accounts by "Numeric S&P Rank Usage" limited to the top 25 highest exposures.  Right now, there are several accounts with the same ranking, so when I do a Top N in the chart, I get 26 instead of 25.  I don't know how to adjust the formula in teh Exp Rank column to achieve this goal.  Please let me know if further clarification is needed.

[v-luwang-msft · Accepted solution]

HI @Anonymous ,

Try to test create new column like the below:

EXP_RANK = RANKX('Table','Table'[S&P Rank Usage]+0.00000000000001*'Table'[Exposure],,DESC,Dense)

It will rank first by S&P Rank Usage,if S&P Rank Usage with the same value ,then rank by  exposure.

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[amitchandak]

@Anonymous , are you looking for Rank Tie breakers ?

Rank Tie breaker
https://community.powerbi.com/t5/Community-Blog/Breaking-Ties-in-Rankings-with-RANKX-Using-Multiple-Columns/ba-p/918655
https://databear.com/how-to-use-the-dax-rankx-function-in-power-bi/

[Anonymous]

No, rather what I would want to see in a new column would be that top row (with S&P Rank 14) to be #1.  Then all of the rows with S&P rank 12 would be #2-6, then the 11's would be #7-9 and so on.  So that there is only one of each number.