RANKX - Ranking Total Sales

[Anonymous]

Hi All,

I have been reading a bit on DAX and trying to work out how the RANKX formula works.

I have the below list of data and trying to rank it by sales. My formula is Sales Rank = RANKX(ALL(Sales),[Total Sales])

The below is the result I get (have used Excel in this example) 

I have tried to use Sales Rank = RANKX(ALL(Sales[Client]),[Total Sales]) which works properly but it doesn't work when I change the pivottable rows to Sector. Is there a reason why I can't use ALL on the entire table?

Client	Sector	Sales
A	F&B	125
B	Tech	456
A	F&B	98
B	Tech	546
C	Tech	564
C	Tech	132
D	Auto	189
E	Financial	456
E	Financial	542
E	Financial	652
E	Financial	745
F	Energy	897
F	Energy	564
F	Energy	132
G	F&B	52
G	F&B	54
G	F&B	87
G	F&B	98
G	F&B	132

Thanks All!

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

Please update your formula as below.

Measure = RANKX(ALL(Sales[Client]),[Total sales],,DESC,Dense)

[Anonymous]

Thanks for your help. If I want to rank total sales by sector, I need to write another DAX formula as below?

Measure = RANKX(ALL(Sales[Sector]),[Total sales],,DESC,Dense)

[amitchandak]

In case you take all

Rank = RANKX(all(Sheet1[Client]),CALCULATE(SUM(Sheet1[Sales])),,DESC,Dense)

Rank will change with another field you add and give a ranking of the client inside that group.

What is expected outcome

[Anonymous]

Hi

I am just trying to understand why Sales Rank = RANKX(ALL(Sales),[Total Sales]) doesn't work

Can I not just take the entire sales table?

If I wanted to group by sector, I need to write another DAX formula like: Sales Rank = RANKX(ALL(Sales[Sector]),[Total Sales])

[az38]

Hi @Anonymous 

how do you get [Total Sales] measure?

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Anonymous]

Total Sales = Sum(SalesTable[Sales])