Show items that make up x% of the sales

[Johan]

Hi,

I'm looking for the dax to show only the items (and their quantities) that make up e.g. 60% of the sales.
Example

item table

ItemID,Item name
1,Parfume
2,Shower foam
3,After shave
4,Soap

sales table

ItemID,Store,Qty.
1,A,110
1,B,310
1,C,510
2,A,120
2,C,320
3,A,130
3,B,230
3,C,330
3,D,430
4,A,240
4,B,440

Outcome should be

After shave  1120 qty

Parfume      930 qty

Anyone has an idea?

Thanks,

Johan

[MFelix]

Hi @Johan ,

Need to create a pareto kind calculation

Following this blog post and making some additional changes you can do the following.

so you can have the cumulated percentage create the following measures:

Total_Qty = SUM(Sales[Qty])

Qty_All_Item = CALCULATE([TotalQty];ALL('Item'[Item Name]))

Item_Rank = RANKX(ALL('Item'[Item Name]);[Total_Qty])

Cumulative_Qty = SUMX(TOPN([Item_Rank];ALL('Item'[Item Name]);[Total_Qty]);[Total_Qty])


Cumulative_%_Qty = DIVIDE([Cumulative_Qty];[Qty_All_Item];BLANK())


Filter_Result = IF([Cumulative_%_Qty] <= 0,6; [Total_Qty];BLANK())

If you want you can also add a parameter table that defines the % for wich you want to show products change last measure by this one:

Final_Result = IF([Cumulative_%_Qty] <= [% Products Value]; [Total_Qty];BLANK())

Now place on a table the Item name and the Final result measure:

Check attach PBIX.

Regards,

MFelix

[Johan]

Excellent solutions, my compliments.

Only thing is that my sales table has 17mln rows and the sumx is eating the memory. Any change to avoid that? For the rest it's exactly what I'm looking for. Thanks.

[MFelix · Accepted solution]

Hi @Johan ,

Based on the option given by @marcorusso  create the following calculated columns on your items table:

% total = CALCULATE(SUM(Sales[Qty]);RELATEDTABLE(Sales))/SUM(Sales[Qty])

Ranking = RANKX('Item';CALCULATE(SUM(Sales[Qty])))


Cumulative % = CALCULATE(SUM('Item'[% total]);ALL('Item');'Item'[Ranking] <= EARLIER('Item'[Ranking]))

Then use the following measure:

Final_Result = IF(SUM('Item'[Cumulative %]) <= [% Products Value];[Total_Qty];BLANK())

The previous measure reference below will not be used so if you want you can deleted them:

• Cumulative%
• Cumulative_qty
• Item_rank
• Qty_All_Item

See file attach.

Regards,

MFelix

[Johan]

Thanks both for you input and solution.

I have now precalculated the accumulated sales in the datawarehouse, and the pareto-dax in the report and that works fine. 

[MFelix]

Hi @Johan

Didn't realize size of your data.

I was reading about performance on SQLBI site and I'm reaching to @marcorusso that wrote an article about this.

https://www.sqlbi.com/articles/optimizing-dax-expressions-involving-multiple-measures/

@marcorusso can you check how the measures can be optimize or if this is the best option?

Thank you. 

Regards

MFelix

[marcorusso]

You should precalculate the cumulated value to avoid the memory materialization - but you loose the flexibility of having the dynamic calculation now that responds to any other filter/slicer in the report.

[MFelix]

Hi @marcorusso ,

Thank you for the response, so if I understand correctly and looking at the size of the model a good alternative is to make a calculated table or add a calculated column on the items table with the sum of sales and/or percentage of total sales?

Regards,

MFelix

[marcorusso]

Correct!