Measure to rank off two columns

[Anonymous]

My data has the following structure:

Account	Exposure	Credit Rating
ABC	22M	25%
CatDog	21M	14%
Elephant	30M	14%
Dinosaur	28M	8%
Spider	18M	8%

I want a rank measure that uses both credit rating and exposure, and also filtering on exposure >

In this example, the ranks would be:

Account	Rank
ABC	1
CatDog	3
Elephant	2
Dinosaur	4
Spider	(blank)

[v-yangliu-msft · Accepted solution]

Hi  @Anonymous ,

Here are the steps you can follow：

1. Create calculated column.

Rank_ex =
IF(
    'RankTable'[Exposure]>=20000000,
RANKX('RankTable','RankTable'[Exposure],,ASC),BLANK())

Rank_cre =
IF(
    'RankTable'[Exposure]>=20000000,
RANKX('RankTable','RankTable'[Credit Rating],,ASC),BLANK())

Ranx3 =
'RankTable'[Rank_ex]+'RankTable'[Rank_cre]+'RankTable'[Rank_cre]

2. Create measure.

Rank =
IF(
    MAX('RankTable'[Ranx3])=BLANK(),BLANK(),
RANKX(ALL('RankTable'),CALCULATE(SUM('RankTable'[Ranx3])),,DESC))

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[TheoC]

Hi @iab_pbi 

Refer to this article: https://www.sqlbi.com/articles/rankx-on-multiple-columns-with-dax-and-power-bi/

Another way to achieve what you're after is by doing the following calculated column:

Ranking = 

RANKX ( 
    ALLSELECTED ( 'Table'[Account] ) , 
        CALCULATE ( SUM ( 'Table'[Exposure] ) - ( SUM ( 'Table'[Exposure] ) * SUM ( 'Table'[Credit Rating] )
      )

I've basically applied the logic that the Exposure Amount less the risk factor associated with the Credit Rating (i.e. Risk Factor = Exposure Amount - ( Exposure Amount * Credit Rating ).  You can modify how you prefer.

All the best.

Theo

[Anonymous]

I tried that suggestion but it didn't work.  If I use this formula, I get the right accounts showing up, but I need to also add in ranking by exposure.

Rank S&P Usage =

var currentqtr = CALCULATE(MAX(Account_Data[Rank Quarter]),ALL(Account_Data))

return

CALCULATE(RANKX(ALLSELECTED(Account_Data[Mapped Account Name]),CALCULATE(SUMX(Account_Data,Account_Data[Numeric S&P with Median]),Account_Data[rank Quarter]=currentqtr,Account_Data[Exposure]>=20000000)))

How do I add in ranking by exposure as well?