Forum Discussion

chiu2020's avatar
chiu2020
Frequent Visitor
4 years ago
Solved

Ranking within a group

Hi all,

 

Below matrix table is showing Parent SKU sales by customer.

 

My DAX formula can rank by Parent SKU correctly. However, I don't know how to do the correct ranking within the sub group (by customer) for each Parent SKU.

 

Rank by $ = RANKX(ALL('SKU Master'[Parent SKU]),CALCULATE('Invoiced Sales Measure'[Invoiced Sales $],ALL(Customer[Customer Group])) ,,DESC,Skip)

 

Can anyone help? Thanks.

DAX
Need Help
ranking
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    Please check the below picture and the attached pbix file.

    I tried to create a sample pbix file like below, and I hope the below can provide some ideas on how to create a solution for your dataset.

     

     

     

     

    Sales total: = 
SUM( Sales[Sales] )


Rank by sales: =
VAR _SKUranking =
    RANKX ( ALL ( 'SKU Master'[Parent SKU] ), [Sales total:],, DESC )
VAR _CustomerslistbySKU =
    FILTER (
        SUMMARIZE ( ALL ( Sales ), Customer[Customer], 'SKU Master'[Parent SKU] ),
        'SKU Master'[Parent SKU] = MAX ( 'SKU Master'[Parent SKU] )
    )
VAR _Customersranking =
    RANKX ( _CustomerslistbySKU, [Sales total:],, DESC )
RETURN
    SWITCH (
        TRUE (),
        ISINSCOPE ( Customer[Customer] ), _Customersranking,
        ISINSCOPE ( 'SKU Master'[Parent SKU] ), _SKUranking
    )

     

3 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    Please check the below picture and the attached pbix file.

    I tried to create a sample pbix file like below, and I hope the below can provide some ideas on how to create a solution for your dataset.

     

     

     

     

    Sales total: = 
SUM( Sales[Sales] )


Rank by sales: =
VAR _SKUranking =
    RANKX ( ALL ( 'SKU Master'[Parent SKU] ), [Sales total:],, DESC )
VAR _CustomerslistbySKU =
    FILTER (
        SUMMARIZE ( ALL ( Sales ), Customer[Customer], 'SKU Master'[Parent SKU] ),
        'SKU Master'[Parent SKU] = MAX ( 'SKU Master'[Parent SKU] )
    )
VAR _Customersranking =
    RANKX ( _CustomerslistbySKU, [Sales total:],, DESC )
RETURN
    SWITCH (
        TRUE (),
        ISINSCOPE ( Customer[Customer] ), _Customersranking,
        ISINSCOPE ( 'SKU Master'[Parent SKU] ), _SKUranking
    )

     

    • chiu2020's avatar
      chiu2020
      Frequent Visitor
      4 years ago

      Thanks for that. It is very helpful

      May I also know how to add the ranking as a slicer? Thanks.

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Icon for Super User rankSuper User
        4 years ago

        Hi,

        Thank you for your feedback. May I ask how is your ranking slicer look like and what does it do?

        For instance, if it selects 3, do you want to only show ranking number 3?