Rank by Highest Sales / Lowest Inventory

[Anonymous]

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?

Sales	Inventory
40.38	260
16.67	6
55.81	43
41.67	12
65.71	35
71.05	38
73.15	149
50	8
33.33	3
73.91	46
63.64	11
0	0
0	0
0	0
84.21	19
50	4
94.12	17
91.67	12
71.43	7
90	10
100	3
100	2
100	2
100	1
100	1
100	1

[v-kelly-msft · Accepted solution]

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:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak]

@Anonymous , 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/367415