RANKX over 2 columns

[gluizqueiroz]

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.

[Anonymous · Accepted solution]

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:

1. Group the table by Group, and want All Rows as the aggregation
2. 
3. 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 🙂

[parry2k · Accepted solution]

@gluizqueiroz  add following column in your model and this will do

Rank = RANKX(  ALL( Table5 ) ,
    Table5[Qty Sales], , DESC, Dense )

here is the output

[Zubair_Muhammad · Accepted solution]

@gluizqueiroz 

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]

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

[parry2k]

@gluizqueiroz well then you have solution in place 🙂

[Marc_TrueLime]

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.

[Anonymous · Accepted solution]

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:

1. Group the table by Group, and want All Rows as the aggregation
2. 
3. 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 🙂

[StephenF]

Anyone else use this approach. The steps here arent clear however.

[parry2k · Accepted solution]

@gluizqueiroz  add following column in your model and this will do

Rank = RANKX(  ALL( Table5 ) ,
    Table5[Qty Sales], , DESC, Dense )

here is the output