Forum Discussion

gluizqueiroz's avatar
gluizqueiroz
Resolver I
7 years ago
Solved

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 ...
  • 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

     

  • Zubair_Muhammad's avatar
    7 years ago

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