In one of my previous posts, I have conducted an ABC Classification analysis using MDX against a SSAS Cube. In this post, I will conduct an ABC Classification analysis using Tableau calculations.
In this example, we want to classify the products based on their sales amount into three categories: ‘A’ (make up the top 5% of total sales), ‘B’ (make up the top %5-20%), ‘C’ (the others).
Firstly, we add the Product Name attribute and Sales Amount measure onto a tableau table and sort the table by Sales Amount in Desc order.
We then create three calculations, “Sales Running Total”, “% Sales Running Total”, and “ABC Classification Category”:
1. [Sales Running Total]
This calculation sums up the total sales amount from the top ranked product to the product of current row.
2. [% Sales Running Total]
While the [Sales Running Total] is divided by the total sales of all products, we can have the related position of the current product against all products.
3. [ABC Classification Category]
This calculation checks the position of the current product in the sales amount range and place the product into the corresponding category. Tableau LOOKUP function is used to evaluate whether the total sales of all the higher ranked products above the current product is < 5% of total sales of all products, if so, the current product falls into the category ‘A’, if the total sales of all the higher ranked products > or = 5%, that means the current product does not consist of the top 5% product sales amount and it will falls into the category ‘B’ or ‘C’. Then we can use the same approach to find which product falls into category ‘B’ and the remaining ones will be in category ‘C’.
We can then add the calculations into the table and have the products mapped to the categories.
or a nice little chart: