THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

Please visit the same blog at - comments are now disabled here but you can comment on the new blog (all blog posts available here are available there, too).
You can follow me on Twitter: @marcorus

ABC Analysis in #dax: complete pattern and other links #powerpivot #tabular

I recently published the ABC Classification article in, which is a more structured and organized way that recap what I already described in this blog a few years ago (see ABC Analysis in PowerPivot). The pattern describe how to implement the classification through calculated columns, so we consider it a specialization of the Static Segmentation pattern. You can implement it also as a measure, implementing a Dynamic Segmentation, and Gerhard Brueckl already described such implementation in his blog. I am not sure about creating a pattern for the dynamic version, because of the performance issue that could arise even with a few thousands of items to classify.

Any feedback on this is welcome, we already have other patterns in the working, but we can always change prioritization based on comments!

Published Thursday, April 17, 2014 1:45 PM by Marco Russo (SQLBI)
Filed under: , , ,



Mikko Lipsanen said:

Hi Marco,

Thank you for the great article! I have your books (Excel and SQL server 2012) and started to implement ABC analysis.

I have added calculated columns (total/running total/%) into the Customer table and the sales data is on Sales table with many to one relation to the Customer table.

I have a timeline in Sales table to select sales in certain time period. However this filter is not propagated into the calculated columns resulting that ABC analysis is based on the whole contents of the DB.

Is there a way to propagate the Sales table filter in the calculated Total Sales in Customer table?

Thank you for your help!


Mikko Lipsanen

April 23, 2014 5:38 AM

Marco Russo (SQLBI) said:


in order to filter data used in ABC classification, you have to use the dynamic segmentation.

As I wrote in the blog post, Gerhard Brueckl described a possible implementation on his blog (follow the link in the blog post). However, I know that performance might be very bad and I don't have so many use case that justify that. Usually the number of filters you might want to implement is limited, and it's better to create different calculated columns, one for each filtered classification.


April 23, 2014 6:55 AM

Mikko Lipsanen said:

Hi Marco,

Thank you for your answer! I guess I have to create the date filtering when loading the data (which is not so dynamic as I have hoped).

Have a nice day!


Mikko Lipsanen

April 23, 2014 7:06 AM
New Comments to this post are disabled

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog



Privacy Statement