cancel
Showing results for
Did you mean:
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?

 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
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
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:

For the related .pbix file,pls see attached.

Best Regards,
Kelly
2 REPLIES 2
Highlighted
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

Proud to be a Super User!

Highlighted
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:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

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