cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
timsleeper
Frequent Visitor

Pareto 80/20 calculation taking too long

I got a sales table and wish to calculate which products correspond to 80/20 of the sales. Each product should be categorized within the selected filters.

 

I managed to get it working using the following code:

'Calculate the total amount sold
AmountAllProduct:= CALCULATE([TotalAmount];ALL(Product[ProductCode];Product[ProductName]))
'Ranks the products according to amount sold
ProductRank:=RANKX(ALL(Product[ProductCode];Product[ProductName]);[TotalAmount])
'Creates an accumulated sum according to the ranking calculated above
ParetoValueProduct:=SUMX(TOPN([ProductRank]; ALL(Product[ProductCode];Product[ProductName]);[TotalAmount]);[TotalAmount])
'Simple pareto. The accumulated sum over total amount
Pareto%Product:=DIVIDE([ParetoValueProduct];[AmountAllProduct];BLANK())

'80/20 flag
80/20 = IF([Pareto%Product]<0.8,"Yes","No)

The credits on this code go to Michiel Rozema, a guest on Dutch Data Dude:

http://dutchdatadude.com/power-bi-pro-tip-pareto-analysis-with-dax/

 

It works, but it's taking TOO LONG and quite often I get and error both on desktop and on the web version of Power BI.  My guess is that the accumulated sum is consuming all the resources but still, I consider myself a DAX noob to come up with a better solution.

 

Anyone could shed some light on alternatives to this code?

 

Besides, I was thinking if there's anything else I could do to improve such move the data to azure cloud or similar alternatives. Since all calculation (I believe) is done inside Power BI, I see little help using Microsoft Azure to improve something. Anyone knows anything about it?

 

Cheers,

Tim

5 REPLIES 5
ImkeF
Super User
Super User

This is a variation of a dynamic ABC-Classification. You can find a different approach here. Worth checking out if it performs better.

But they are all using iterators which can be slow on large tables.

 

If the users shall slice on the time-dimensions, then there is no way around those dynamic approaches that use measures, otherswise you could use the static pattern, that uses calculated columns - shifting workload to the loading phase. (Or use M-code).

 

Biggest impact on performance might lie in optimizing your underlying table:

1) Remove unnecessary columns: It should only contain the columns that are addressed in your code (and the keys needed to connect to necessary lookup-tables)

2) Remove unnecessary rows: Aggregate. So if this is part of a bigger datamodel where you currently adress big fact tables on a granularity level that is not necessary for the Pareto-calculation, it could make sense to create a separate aggregated table in the query editor (using M-code) that is just used for this task, if it significantly reduces the iteration-efford.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

timsleeper
Frequent Visitor

Thanks Imke. The link you gave me performs better but is not working properly with filters. Once calculated, a particular SKU is set with one classification (let's say "A") and stays that way no matter what filter I set.

 

I wish that once a filter is set, for Store 1 for example, the whole ABC classification should be redone, considering the products sold on that Store 1. Same thing with categories, salesmen, etc...

 

Time-dimensions is not an issue, at least right now.

Hi Timesleeper,

sorry, completely missed your response.

 

Strange that it accepts filters once & then doesn't change. Cannot think of a reason at the moment.

 

But one thing in the code you've posted seems to be wrong: Your using ProductCode and ProductName. You should definitely restrict it to the key column only.

 

Otherwise: Would you mind sharing the code you've created for the dynamic pattern?

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Is this link accessible? http://dutchdatadude.com/power-bi-pro-tip-pareto-analysis-with-dax/

 

I am not able to open it.

 

 

Sean
Community Champion
Community Champion

@timsleeper Are you still having issues with the Pareto Charts taking too long?

 

What versions of PBI and Windows are you using?

 

Please respond here...

http://community.powerbi.com/t5/Desktop/Pareto-80-20-calculation-for-items-making-up-80-of-Sales/m-p...

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!