RANX returns same ranking for two different values

[Kunder]

This is my table 

Customer	Sales
A	6
B	13
D	5
E	7
R	8
G	2
A	9

This is what I get from the DAX below :

=RANKX(ALL(Sheet1),Sheet1[Totalsales])

where [Totalsales] is another measure with the calculation =SUM(Sheet1[Sales])

Why duplicates here? please help

[raimon · Accepted solution]

Hi @Kunder ,

If you are creating a measure for Rank, please use this - 

Rank Measure = RANKX(ALL('Table'[Customer]),CALCULATE(SUM('Table'[Sales])),,DESC)

Else, if you want to create a column, first need to create another column for total sales. 

Total Sales = CALCULATE(SUM('Table'[Sales]),FILTER('Table','Table'[Customer]=EARLIER('Table'[Customer])))

Rank Column = RANKX('Table','Table'[Total Sales],,desc)

 

[Viswanathan123]

i have 4 columns named customername, department, country and profit. i am trying to rank based on profit it is giving me unique ranks for the whole table . What i need help with is if i am filtering a department named finance it contains 20 rows i need the ranks to be created new for that department based on the profit not the ranks from the whole table for the filtered department alone i need the ranks to start from begining any suggestions for that.
can we use RANKX inside another RANKX?

[Thejeswar]

Hi @Viswanathan123 ,

If you are using the ALL() function, the RANK will not be recalculated for the slicer selection. Instead use the below DAX using ALLSELECTED() DAX function

Rank = RANKX(ALLSELECTED('Table'), [TotalProfit],,DESC,Dense)

Before applying filter on department

After applying filter on department

Regards,

[raimon · Accepted solution]

Hi @Kunder ,

If you are creating a measure for Rank, please use this - 

Rank Measure = RANKX(ALL('Table'[Customer]),CALCULATE(SUM('Table'[Sales])),,DESC)

Else, if you want to create a column, first need to create another column for total sales. 

Total Sales = CALCULATE(SUM('Table'[Sales]),FILTER('Table','Table'[Customer]=EARLIER('Table'[Customer])))

Rank Column = RANKX('Table','Table'[Total Sales],,desc)

 

[Thejeswar]

Hi @Kunder ,

You need to modify your RANKX as follows

RankMeasure = RANKX(ALL('Table'[Customer]), CALCULATE(SUM('Table'[Sales])),,DESC)

. Here RANKX is written inside a calculated Measure

If you don't want to see the Rank in Total row, use the below DAX

RankMeasure = IF(HASONEFILTER('Table'[Customer]), RANKX(ALL('Table'[Customer]), CALCULATE(SUM('Table'[Sales])),,DESC))

[Kunder]

@Thejeswar - This worked. One last thing. Is CALCULATE mandatory here? and why?

[Thejeswar · Accepted solution]

@Kunder ,

Yes !! If you want to avoid using Calculate, you may try to use a separate measure, where you get the sales values. Then use it inside the RANKX function.

The Below post might give you more info on this

https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures

[SanketBhagwat]

Hi @Kunder .
Try the below DAX;
Rank=RANKX(Table,Table[TotalSales],,DESC)

Thanks,
Sanket.


If this post helps you, then please mark it as 'Accept as Solution' and give it a thumbs up.

[Kunder]

@SanketBhagwat  -I get '1' for all ranks

[SanketBhagwat]

Can you try this measure,

Rank = RANKX(ALL('Table1'), CALCULATE(SUM(Table1[TotalSales])),,DESC)