Tableau LOD UseCase#3 : LOD Comparative Analysis

During my Corporate Tableau Training in Gurgaon , i get questions many time regarding LOD Comparative Analysis in Tableau . If we want to do a comparative Analysis of specific versus total in the same visualization, we can make use of LOD as below.

For the better understanding of the situation analyze this from the sample superstore data set i.e. We have to analyze the Sales of our various Sub Categories in all the 4 region and also their Sales in the Selected regions.

For Corporate training and Online Training contact at info@instrovate.com

The End Result that we are expecting is as below:

So The Question that we are seeking an Answer is as below

Question: How much Sales is Phones making in all the 4 Regions i.e. East, West, North, South and what is the Comparative Sales of Phones in Central & East as compared to all the 4 Regions.

Step-1:

Lets’ first make a viz with Region and Sales in Column & Sub Categories in Rows. Usually, this is what we will make when we are asked about the Sales of all the Sub Category in various Regions, right? So. The result will be like image below:

Now, let’s analyze the second part of our question: The Sales of various Sub Category in the Selected region- Center & East.

So, we’ll drop the Region to Filters and choose Center & East. The result will be as below:

But, by doing this we have missed the first part of the question: The Sales of all the Sub Category in all the 4 Region. Remember we have to analyze: How much Sales is Phones making in all the 4 Regions i.e. East, West, North, South and what is the Comparative Sales of Phones in Central & East as compared to all the 4 Regions.

That means we want a Dual Axis chart showing Sales in various Region and also in Selected Region. So, let’s move to Step 2.

Step-2:

Open a Calculated Field and name it ‘Sub Category Sales LOD’ with the following formula:

So, what exactly are we trying to do here is: we are Fixing the Sales of Sub Categories by this LOD Calculation. That means that whenever we use this ‘Sub Category Sales LOD’ field any filters will not effect the value of Sales as it is Fixed. So, it will give the total Sales of all the Sub Category irrespective of any filters.

Step-3:

Let’s make a viz using this LOD Calculation and see what happens.

In a new sheet, Drag Sub category Sales LOD & sales to Column and Sub category to Rows. Put Regions to Filter shelf and choose Center & East.

If you notice the Marks shelf you notice that there are 3 marks cards: All, Sum(Sub Category Sales LOD), Sum(Sales). Put Sub Category Sales LOD from the data pane to the labels in Sum(Sub Category Sales LOD) marks shelf and Sales from the data pane to the labels of Sum(Sales). The result will be as below:

Step-4:

Now Right click on Sum(Sales) on the Column shelf to open a drop down and choose dual axis. Then from All in the Marks shelf choose Bars from Automatic drop down.

From pane click on T to remove Labels.The result will be as below:

Also Right click on the Axis at top and select “Synchronize Axis” to get both the Axis synchronized and see the Comparison clearly as below:

Also right click on the Top Axis and de-select “show header” to remove it ,if you would like to show only one axis after the Axis are synchronized.

Step- 5:

The final formatting: drop Sales and Sub Category Sales LOD from the data pane to the Detail Shelf of All card, Click on Tooltip and make the following changes in the tooltip dialogue box.

So This completes our comparative Analysis and in one single visualization, we are able to see the sales of the selected region and also the total sales. And we finally get the answer to our Question: