Pareto 80/20 calculation taking too long

[timsleeper]

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

[ImkeF]

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.

[timsleeper]

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.

[ImkeF]

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?

[vanessa]

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

I am not able to open it.

[Sean]

@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...