Forum Discussion

gluizqueiroz's avatar
gluizqueiroz
Resolver I
7 years ago
Solved

RANKX over 2 columns

I have the following table:

 

ProductGroupQty Sales
MousePC15
KeyboardPC20
MonitorPC12
CablePC28
BananaFruits64
AppleFruits52
WatermelonFruits41
OvenKitchen  Appliances5
FridgeKitchen  Appliances3
Microwave ovenKitchen  Appliances4

 

I need to make a RANKX about Products but ignoring the column Group, like the following table:

 

RANKXProductGroupQty Sales
1BananaFruits64
2AppleFruits52
3WatermelonFruits41
4CablePC28
5KeyboardPC20
6MousePC15
7MonitorPC12
8OvenKitchen  Appliances5
9Microwave ovenKitchen  Appliances4
10FridgeKitchen  Appliances3

 

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's avatar
    Anonymous
    7 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:

    1. Group the table by Group, and want All Rows as the aggregation
    2. 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's avatar
    parry2k
    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

     

  • 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] ) )
    )
    

7 Replies

  • Anonymous's avatar
    Anonymous
    Not 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:

    1. Group the table by Group, and want All Rows as the aggregation
    2. 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's avatar
      parry2k
      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

       

    • StephenF's avatar
      StephenF
      Responsive Resident

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

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion

    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's avatar
      gluizqueiroz
      Resolver I

      Hey Zubair_Muhammadparry2k  and Anonymous.

      I tried all solutions ang I got the same result in all of them:

      RANKXProductGroupQty Sales
      1BananaFruits64
      1CablePC28
      1OvenKitchen  Appliances5
      2AppleFruits52
      2KeyboardPC20
      2Microwave ovenKitchen  Appliances4
      3WatermelonFruits41
      3MousePC15
      3FridgeKitchen  Appliances3
      4MonitorPC12