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] ) ) )
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 :)
- parry2k7 years ago
Super User
gluizqueiroz add following column in your model and this will do
Rank = RANKX( ALL( Table5 ) , Table5[Qty Sales], , DESC, Dense )here is the output
- StephenF4 years ago
Responsive Resident
Anyone else use this approach. The steps here arent clear however.