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:
In the last blog post, I’ve created a box-and-whisker plot to compare UK’s performance on Eurovision contest with others. That would be good to have a nice Viz to show where the votes from for each country. This can be achieved using Tableau Map and Path.
It is straightforward to create this Viz using the Path Shelf on Tableau. However, we need have the source data in the right structure. The snapshot below shows the structure of original raw data for Eurovision final voting. Each vote is stored in a single row with the [Country] column storing where the score was given to and the [Giver] column storing where the score was given from.
To use the Path feature in Tableau, we need transform the vote row into two rows, one row stores details for the start point of the path and the other is for the destination point of the path. the snapshot below shows the required data structure for the Viz.
To transform the data structure, I have loaded the original Eurovision voting data into SQL Server and create a Stored Procedure to output the data in the required structure.
The original vote row is UNPIVOT on the [Country] and [Giver] column and generate two rows with details on where the vote is from and to. For each path pair, we need give them a unique [Path ID] and also assign the [Path Order] to the Start and To row. We can have the actual [Score] number on the [Giver] row so that we can assign the SUM([Score]) value to Size of the Start point to represent the size of voting score.
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.