Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to 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.
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.
@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
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |