Forum Discussion
RANKX over 2 columns
I have the following table:
| Product | Group | Qty Sales |
| Mouse | PC | 15 |
| Keyboard | PC | 20 |
| Monitor | PC | 12 |
| Cable | PC | 28 |
| Banana | Fruits | 64 |
| Apple | Fruits | 52 |
| Watermelon | Fruits | 41 |
| Oven | Kitchen Appliances | 5 |
| Fridge | Kitchen Appliances | 3 |
| Microwave oven | Kitchen Appliances | 4 |
I need to make a RANKX about Products but ignoring the column Group, like the following table:
| RANKX | Product | Group | Qty Sales |
| 1 | Banana | Fruits | 64 |
| 2 | Apple | Fruits | 52 |
| 3 | Watermelon | Fruits | 41 |
| 4 | Cable | PC | 28 |
| 5 | Keyboard | PC | 20 |
| 6 | Mouse | PC | 15 |
| 7 | Monitor | PC | 12 |
| 8 | Oven | Kitchen Appliances | 5 |
| 9 | Microwave oven | Kitchen Appliances | 4 |
| 10 | Fridge | Kitchen Appliances | 3 |
If I make a basic measure like the following:
Ranking = RANKX(ALLSELECTED(Fact_Sales[Product]);[QtySakes])
PowerBI will calculate a rank over Product but considering Group, in other words, the PowerBI will calculate "3 ranks" in 1 table. 1 rank for Fruits, 1 rank for PC and 1 rank for Kitchen Appliances, and it is not correct for my case.
- 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 :)
gluizqueiroz add following column in your model and this will do
Rank = RANKX( ALL( Table5 ) , Table5[Qty Sales], , DESC, Dense )here is the output
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] ) ) )
7 Replies
- AnonymousNot applicable
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 :)
- parry2kSuper 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
- StephenFResponsive Resident
Anyone else use this approach. The steps here arent clear however.
- Zubair_MuhammadCommunity Champion
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] ) ) )- gluizqueirozResolver I
Hey Zubair_Muhammad, parry2k and Anonymous.
I tried all solutions ang I got the same result in all of them:RANKX Product Group Qty Sales 1 Banana Fruits 64 1 Cable PC 28 1 Oven Kitchen Appliances 5 2 Apple Fruits 52 2 Keyboard PC 20 2 Microwave oven Kitchen Appliances 4 3 Watermelon Fruits 41 3 Mouse PC 15 3 Fridge Kitchen Appliances 3 4 Monitor PC 12 - parry2kSuper User
gluizqueiroz well then you have solution in place :)