cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## RANKX - Ranking Total Sales

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support

## Re: RANKX - Ranking Total Sales

Hi @swongbg ,

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
6 REPLIES 6
Highlighted
Super User IV

## Re: RANKX - Ranking Total Sales

Hi @swongbg

how do you get [Total Sales] measure?

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

do not hesitate to give a kudo to useful posts and mark solutions as solution
Highlighted
Super User IV

## Re: RANKX - Ranking Total Sales

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

Proud to be a Super User!

Highlighted
Frequent Visitor

## Re: RANKX - Ranking Total Sales

Total Sales = Sum(SalesTable[Sales])

Highlighted
Frequent Visitor

## Re: RANKX - Ranking Total Sales

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])

Highlighted
Community Support

## Re: RANKX - Ranking Total Sales

Hi @swongbg ,

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Highlighted
Frequent Visitor

## Re: RANKX - Ranking Total Sales

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)``

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

#### Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors