RANKX base on other columns

[ngct1112]

Hi all, I am trying to rank (from lowest to highest) a column as below

1.) Ranking of Products(ASC): Ranking the "Products" from highest to lowest

This is working -

Ranking of Products(ASC)= RANKX( ALLSELECTED(Table),CALCULATE(SUM('Table'[Products])))

2.)Ranking of cost(DESC): Ranking the "cost" only when "Ranking of Products(ASC)" = 1

This is not working -

Ranking of cost(DESC) = IF([Ranking of Products(ASC)]=1,RANKX( ALLSELECTED(Table),CALCULATE(SUM('Table'[cost])),,ASC))

Items	Products	Lowest Cost
A	5	12
B	5	8
C	5	5
D	4	4
E	2	7

Desired Outcome:

Items	Products	Cost		Ranking of Products(ASC)	Ranking of cost(DESC)
A	5	12		1	3
B	5	8		1	2
C	5	5		1	1
D	4	4		3
E	2	7		2

Could I have some advices on the second measure? Great thanks!

[amitchandak · Accepted solution]

@ngct1112 , Try like

new Rank = RANKX( filter(ALLSELECTED(Table[Items],Table[Products]), Table[Products] =max(Table[Products])), CALCULATE(SUM('Table'[cost])),,ASC)

Ranking of cost(DESC) = IF([Ranking of Products(ASC)]=1,[new Rank ], blank())

[v-xicai · Accepted solution]

Hi @ngct1112 ,

For the logical of DESC order and ASC order, it may be wrong in the original post. And I have correct it in the following formulas .

You may create measures like DAX below.

Ranking of Products(DESC) = RANKX( ALLSELECTED('Table'),CALCULATE(SUM('Table'[Products])), ,DESC,Dense)


Ranking of cost(ASC) = IF('Table'[Ranking of Products(DESC)]=1, RANKX( FILTER(ALLSELECTED('Table'), 'Table'[Ranking of Products(DESC)]=1),CALCULATE(SUM('Table'[cost])),,ASC,Dense))

Best Regards,

Amy 

Community Support Team _ Amy

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

[Anonymous]

• If expression or value evaluates to BLANK it is treated as a 0 (zero) for all expressions that result in a number, or as an empty text for all text expressions.

• If value is not among all possible values of expression then RANKX temporarily adds value to the values from expression and re-evaluates RANKX to determine the proper rank of value.

• Optional arguments might be skipped by placing an empty comma (,) in the argument list, i.e. RANKX(Inventory, [InventoryCost],,,"Dense")

• This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

[amitchandak · Accepted solution]

@ngct1112 , Try like

new Rank = RANKX( filter(ALLSELECTED(Table[Items],Table[Products]), Table[Products] =max(Table[Products])), CALCULATE(SUM('Table'[cost])),,ASC)

Ranking of cost(DESC) = IF([Ranking of Products(ASC)]=1,[new Rank ], blank())