Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.