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:

Eurovision is my favourite song contest programme, not only because of the not bad songs but also the acid humour from the BBC presenter Terry Wogan. I have been watching this show for a few years, from my memory, UK has constantly performed poorly on the final voting which has been blamed as “tactical Eastern bloc voting” by Terry Wogan.

As I came across the raw data of Eurovision results (from 1998 to 2012) a few days ago, it gives me an opportunities to feed my curiosity on how UK has performed in the contest statistically.

I loaded the raw data into Tableau and create a box-and-whisker plot to compare the average place of each country participated in the contest.

From the chart, we can see that UK did perform below average and ranked 9th from bottom with France, Portugal etl. countries below it. The average rank of UK is 16.33, below the overall average rank of all countries (12.70).

From the box-and-whisker plot of UK, we can see that UK’s ranks in the contest from 1998 to 2012 have wide spread from the 2nd place to 26th place. The median of the ranks is 16 and the lower quartile is 11.50 which makes UK at the average-below level compared to other countries. However, the interesting part is the upper quartile which is as high as 24 which indicates that UK has been at the bottom of the contest for quite a few years. In comparison to the box plot of France’s and Portugal’s, although both average and median of the two countries’s ranks are lower than UK’s, the distribution of the ranks are less spread and the upper quartile of the two countries’ are lower than UK’s. That may explain why my perception of UK’s performance on Eurovision is worse than those two countries.