Almost two years ago I published the first version of an Dynamic ABC Analysis in Power Pivot and by now it is the post with the most comments among all my blog posts. This has two reason:
1) the formula was quite complex and not easy to understand or implement
2) the performance was not really great with bigger datasets
When the first of those comments flew in, I started investigating into a new, advanced formula. At about the same time Marco Russo and Alberto Ferrari published their ABC Classification pattern – a static version using calculated columns – at www.daxpatterns.com. When I had my first dynamic version ready I sent it to Marco and Alberto and asked if they are interested in the pattern and if I can publish it on their website. Long story short – this week the new pattern was released and can now be found here:
It got some major performance improvements and was also designed towards reusability with other models. The article also contains some detailed explanations how the formula actually works but its still very hard DAX which will take some time to be fully understood. The pattern also features some extended versions to address more specific requirements but I think its best to just read the article on your own.
Hope you enjoy it!
Hi Gerhard,
I am using your Dynamic ABC pattern and it’s working great, thanks!
However I am facing new challenge now – I need to perform an ABCD classification, where ABC is calculated in the usual way, but D are items with negative contribution.
In other words, if I draw a classic Pareto chart, the cummulated total exceeds 100% at some point (here the category C should and) and continues to drop back to 100% “thanks to” the items/stores/whatever with negative contribution.
So for example I need this classification
A up to 70%
B 70 to 90%
C 90% to 100% (in the above, basis are only the positive items)
D items with negative value measure
Thank you
Martin
Interesting question, I will investigate and give you feedback – however, this might take some time
I will keep you updated here
-gerhard
Hi Gerhard,
Thanks for sharing this! I was wondering if there is a way of plotting the results of the analysis into a new table where we can further manipulate the categories?
The solution so far is to export the matrix of the visual and then import back into the model but surely this could be avoided somehow?
Sof
Well, as it is a dynamic so you cannot import it back again
You could search for static ABC analysis instead
Could you describe the process of your investigation into creating an advanced formula in response to the initial comments, and how that led to the development of a dynamic version of the ABC Classification pattern? Additionally, how did your collaboration with Marco Russo and Alberto Ferrari contribute to the pattern’s release on their website, and where can readers find more information about this newly released pattern?
the latest version is available at DAX patterns: https://www.daxpatterns.com/abc-classification/
to get answers to your other questions please use the contact form as they do not belong here