Ranking within a group

[chiu2020]

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.

[Jihwan_Kim · Accepted solution]

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]

Thanks for that. It is very helpful

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

[Jihwan_Kim]

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?