Sum and rank sales

[HassanI]

Hi,

I have the following calculated table in Power BI (anonymised here), I am trying to sum the total net sales by date, retailer, and brand.

So it looks like this:

The aim is to rank WITHIN each month AND retailer the top 10 brands, and then sum the sales of the rest of the brands within that retailer and month under "others".

My current thinking is to create another calculated table from the original, sum the sales by date, retailer, brand. Then add a calculated column with RANKX function to rank brands by sales within the month and retailer.

Then to add another column (lets call new brand name) with IF to check if rank is between 1 and 10, true = brand name, false = "Other"

Then on a report page add a date slicer and retailer slicer with single selection. Then add a table visual with new brand field and net sales field to show top 10 brands + "Others" and summed sales value.

Is my thinking correct, and if so how would I create the summarised table and rank?

[Moidden123]

Hi,

I have to create a table the same way you sum up all the net sale by date, retailer and brand.  Could you show me how you did this?

Thanks,

Tram

[v-yuta-msft]

Hi HassanI,

You can create measures using DAX like below:

Result = RANKX(ALL(Table1), CALCULATE(SUM(Table1[Sum of Sales]), ALLEXCEPT(Table1, Table1[Date], Table1[Retailer], Table1[Brand])), , DESC)

Check = IF(Table1[Result] > 0 && Table1[Result] <= 10, "Brand Name", "Others")

 

PBIX for your reference: https://www.dropbox.com/s/fpu7bzepr87qbh5/Sum%20and%20rank%20sales.pbix?dl=0.

Regards,

Jimmy Tao

[HassanI]

Hi Jimmy,

Thanks for the DAX measures, however, it does not quite meet the requirements, I need it to rank the brands within the retailer and Month bucket. For example for retailer A in February I need the brands ranked 1 to N by sum of sales for that retailer in that month, then the same for retailler B in feb, and so on by retailer and by month.

[v-yuta-msft · Accepted solution]

Hi HassanI,

OK, you can create a calculate column using DAX as below:

Rank =
RANKX (
    FILTER (
        Table1,
        MONTH ( Table1[Date] ) = MONTH ( EARLIER ( Table1[Date] ) )
            && Table1[Retailer] = EARLIER ( Table1[Retailer] )
    ),
    RANKX ( ALL ( Table1 ), Table1[Sum of Sales] ),
    ,
    ASC,
    DENSE
)

  

PBIX for your reference: https://www.dropbox.com/s/fpu7bzepr87qbh5/Sum%20and%20rank%20sales.pbix?dl=0.

Hope it's helpful to you.

Jimmy Tao

[HassanI]

Thank you this seems to be working well. Will test with additional data.