Forum Discussion
RANKX over 2 columns
- Anonymous7 years ago
You can do this in Power Query with a few steps. Can also be done in DAX, but went with the Power Query version first.
Steps:
- Group the table by Group, and want All Rows as the aggregation
- Add a custom column to sort each sub-table by Sales high to low with this code:
Table.Sort( [All Data], {"Qty Sales", Order.Descending})- Then add another column with will but an idex (or rank in this case) to each subtable starting at one and incrementing from there. This is why we needed to sort in the previous step:
Table.AddIndexColumn( [SortSubTable], "Rank", 1,1)
- Remove all the other columns except the one just created
- Expand that column
- Set data types and sort if you want
Final Table:
But now looking at your request, that may not be what you had in mind! If you want to rank over the entire table this will work:
Rank = RANKX( ALL( BasicTable), [Total Qty],,DESC,Dense)
Hopefully some of that was helpful :)
- 7 years ago
gluizqueiroz add following column in your model and this will do
Rank = RANKX( ALL( Table5 ) , Table5[Qty Sales], , DESC, Dense )here is the output
- 7 years ago
As a MEASURE, we could use
Ranking = RANKX ( ALLSELECTED ( Fact_Sales[Product] ), CALCULATE ( SUM ( Fact_Sales[Qty Sales] ), ALL ( Fact_Sales[Group] ) ), CALCULATE ( SUM ( Fact_Sales[Qty Sales] ) ) )
gluizqueiroz well then you have solution in place :)
Thanks for all your input so far, really helped me out to get the ranking/sorting correct in 99,9% of all cases.
But is there a possibilty to add an extra ordering option which should be used if the totals are identical? If the Total QTY for Banana and Apple is equal I would like to add an extra sort on alpabetical order resulting in Apple with Rank 1 and Banana with Rank 2.
At the moment they both get Rank 1.