Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
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.
@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...
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
100 | |
98 | |
88 | |
70 |
User | Count |
---|---|
166 | |
131 | |
129 | |
102 | |
98 |