cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Rank by Highest Sales / Lowest Inventory

Hi  @Jazzyfey11 ,

 

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

Re: Rank by Highest Sales / Lowest Inventory

@Jazzyfey11 , Create two Rank, Inventory desc Rank, and Sales Asc rank. The top rank is what you need. Plot these Rank on scatter visual with avg line on both x and y to get 4 quadrant

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Community Support
Community Support

Re: Rank by Highest Sales / Lowest Inventory

Hi  @Jazzyfey11 ,

 

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

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors