Mastering Deficit Analysis: Visualizing Insights with Tableau Bar Charts

Understanding Deficit Analysis Charts: Leveraging Bar Chart Techniques in Tableau

Optimizing Insights: Implementing Deficit Analysis in Tableau with Bar Charts

Deficit or known as Surplus Analysis is an especially important concept in the world of Finance. It has a deep impact on a company’s growth and progress. The word Deficit can be used as the noun form of Deficient (Deficiency) meaningless nourishment which is not restricted to food.

In many of the other blogs, we have discussed creating % change calculations and using it for different visualizations with shape and color. In this blog, we will be guiding you on how to build a special Deficit Waterfall chart. It like a traditional waterfall chart but even more simplified. It can have desired shape and/or color or added features too but not mandatory.

Deficit Analysis Fundamentals: Key Concepts and Principles

Crafting Effective Bar Charts: Techniques for Enhanced Visualization

Practical Applications: Real-World Examples of Deficit Analysis in Action

Tips and Tricks: Maximizing the Impact of Tableau Bar Charts for Deficit Analysis

Visualizing Trends: Identifying Deficits and Surpluses with Tableau

Data-driven Decision-Making: Leveraging Bar Charts for Insightful Analysis

Enhancing Clarity: Design Best Practices for Deficit Analysis Bar Charts

Interactive Visualization: Engaging Stakeholders with Dynamic Tableau Dashboards

Deficit and Surplus go hand in hand. However, analysts, in general, refer to the same as Deficit Analysis. The concept is quite simple and straightforward. There is usually a Start Value or the Threshold and an End Value or the Actual. What is the change between the values of Start and End? Did it Increase or Decrease? How can this be represented with a simple intuitive chart?

If the value decreases from the original, it will be called a Deficit (Under-performance). If the value increases, it will be termed Surplus (Over-performance). We will be showing it in this example.  

A Waterfall chart with a single measure is easy in Tableau

Unfortunately, Tableau does not allow for a multi-measure waterfall chart. It needs some data reshaping (Pivoting). If the data is huge, it can lead to bigger issues, especially in the case of aggregate duplicates. That is the reason to pick only 3 measures to create a Deficit Bar Chart in Tableau. Here, we will be building a Bar chart (as a Waterfall representation). The Deficit Bar Chart in Tableau can be represented with the help of a Pie chart which will be discussed in a different blog.

For this example, we will be focusing on Import/Export Analysis. The objective is, if the Country’s Export (End Value for this case) is lower than the Import (Start Value), it is considered a Deficit because every Country’s administration depends on its Trade Revenue apart from taxes, etc. If the country is importing in excess, it may represent a poor economy and lead to debts.

We will be using a simple file with 5 years of data with some random values. Here, we have the Year dimension, Import measure, and Export measure. As explained, we want to show for every year, which value was higher. In case the Import is more, show the Deficit value. In case the Export is more, show the Surplus value.

A Step-by-step Approach to Create Deficit Bar Chart in Tableau

There are a lot of tricks and hacks required for this Deficit Bar Chart in Tableau. Let us go step by step.

1) Convert the Year field into a Dimension and into a Discrete field. This can be done on the view too.

2) Convert the Import and Export measure into a Decimal data type. This will help in the Dual Axis.

3) Create a Vertical Side by Side bar chart for Imports and Exports across all Years

As per this case, the Imports must be on the left and Exports must be on the right. But we will come to that stage of rearranging fields in later steps.

4) Following, we will have to create a difference value between Exports and Imports. The difference here must be calculated always for the latter value (or End Value). At times, some of us make a mistake by calculating the difference from the higher value. In this case, however, we are concerned about Exports because the Deficit value is calculated for the Export.

5) Using this field with the view to confirm the values.

However, in this case, we have two different charts. As seen in the image earlier, the idea is to show the Imports, Change, and Exports together. Hence, we need to bring all three measures together.

We cannot directly go for a Dual Axis. Most of us make a mistake. In case we create a Dual Axis, all three measures will be placed one next to the other which completely defeats the purpose.

Some end-users might feel fine with this approach. However, our idea is to show a hanging or a floating bar for the Deficit or Surplus value.

For this, we need to fill the empty space to place the hanging/floating bar like a foundation. This is where all the strategies come into place. We can create the data to suit this visualization by pivoting it and making it look like a stacked bar and making the bottom half to be white (or background color). But we will be taking a different approach of preparing a placeholder field.

6) Prepare a placeholder using the Start value. Because the Deficit or Surplus is calculated for the End Value (Exports), the base or foundation will be for the Start Value which is Imports in this case. The field can be named anything. Ideally, it can be a Space or -. This measure also must be a decimal. Because the Import field is already a decimal, this field will also pick the same properties.

7) All the steps here on are particularly important. We will have to replace the existing Deficit/Surplus field from Step 5 with the Placeholder.

8) You will need to create a Dual Axis and Synchronize the axes (The axes can be Synchronized only if all the measures are of decimal data type).

Again, there are chances of making mistakes. Many people think that it would be easier to just use the Import measure again. This will not be helpful as it will create a stack or place the measures one behind another when we create the Dual Axis. We require the measures to be placed one beside another (controlled by Measure Names)

9) The following step will be to create the floating bar. Therefore, we need to convert the Marks for the Placeholder to a Gantt chart. The advantage of the Gantt chart, in this case, is that it will act as a small platform at the highest value.

10) Further, we will have to use the Deficit/Surplus calculation that we have created on the Size card. This will control the thickness of the platform. In case the value is positive, it will increase the size upwards. In case the value is negative, the size will increase downwards.

11) Next, we need to arrange the values in order – first Imports, followed by the Deficit/Surplus value, and then Exports. We need to Sort using Measure Names.

We can stop at this stage. However, we will add a couple of more steps so that the end-user has a better picture. Some adjustment in terms of legends and cosmetics is required.

12) Use the Deficit/Surplus field on the label only for the Gantt chart.

13) We Will need to create a conditional calculation to provide color for Deficit or Surplus value. Then using that field on color.

After some aesthetic changes, editing, formatting, etc., we finally arrive at the view.