Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
swongbg
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) 

 

Incorrect Ranking.PNG

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?

 

 

ClientSectorSales
AF&B125
BTech456
AF&B98
BTech546
CTech564
CTech132
DAuto189
EFinancial456
EFinancial542
EFinancial652
EFinancial745
FEnergy897
FEnergy564
FEnergy132
GF&B52
GF&B54
GF&B87
GF&B98
GF&B132

 

Thanks All!

1 ACCEPTED SOLUTION
v-frfei-msft
Community Support
Community Support

Hi @swongbg ,

 

Please update your formula as below.

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

Capture.PNG

 

 

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

View solution in original post

6 REPLIES 6
v-frfei-msft
Community Support
Community Support

Hi @swongbg ,

 

Please update your formula as below.

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

Capture.PNG

 

 

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

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
Super User
Super User

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

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
Community Champion
Community Champion

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
LinkedIn
swongbg
Frequent Visitor

Total Sales = Sum(SalesTable[Sales])

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.