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
Anonymous
Not applicable

Rank by Highest Sales / Lowest Inventory

How can I sort/rank the following data by Highest Inventory/Lowest Sale:
I know I can rank one or the other, I'm trying to figure out a way to combine the 2 to show in order of highest inventory but has the lowest sale amount, so I know which area to focus in on.  Is there a DAX formula that would do a "combined rank" of some sort?

 

 

SalesInventory

40.38

260
16.676
55.8143
41.6712
65.7135
71.0538
73.15149
508
33.333
73.9146
63.6411
00
00
00
84.2119
504
94.1217
91.6712
71.437
9010
1003
1002
1002
1001
1001
1001
1 ACCEPTED SOLUTION
v-kelly-msft
Community Support
Community Support

Hi  @Anonymous ,

 

Create 2 measures as below:

Measure = 
var _rankbysales=RANKX(ALL('Table'),CALCULATE(SUM('Table'[Sales])),,ASC,Dense)
var _rankbyinventory=RANKX(ALL('Table'),CALCULATE(SUM('Table'[Inventory])),,DESC,Dense)/CALCULATE(COUNT('Table'[Sales]),ALL('Table'))
var _ranktotal=_rankbysales+_rankbyinventory
Return
_ranktotal
rankx = RANKX(ALL('Table'),'Table'[Measure],,DESC,Dense)

And you will see:

Annotation 2020-08-06 144534.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

View solution in original post

2 REPLIES 2
v-kelly-msft
Community Support
Community Support

Hi  @Anonymous ,

 

Create 2 measures as below:

Measure = 
var _rankbysales=RANKX(ALL('Table'),CALCULATE(SUM('Table'[Sales])),,ASC,Dense)
var _rankbyinventory=RANKX(ALL('Table'),CALCULATE(SUM('Table'[Inventory])),,DESC,Dense)/CALCULATE(COUNT('Table'[Sales]),ALL('Table'))
var _ranktotal=_rankbysales+_rankbyinventory
Return
_ranktotal
rankx = RANKX(ALL('Table'),'Table'[Measure],,DESC,Dense)

And you will see:

Annotation 2020-08-06 144534.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
amitchandak
Super User
Super User

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.