Dynamic ABC Analysis in Power Pivot using DAX – Part 2

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.

2 Replies to “Dynamic ABC Analysis in Power Pivot using DAX – Part 2”

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