Dual Axis Chart In Tableau - 3 Methods

As discussed in one of the previous blogs, comparison charts are very common and most important charts for any data. Tableau also can create some very special comparison charts. One such widely used and popular chart in excel and other BI tools is a Dual Axis Chart or referred to as Combo Chart (Involves the combination of 2 charts).

The concept of Dual Axis Chart is a little tricky to understand initially. We focus on 2 measures where one measure is superimposed over another measure. Many of us assume that the second measure is placed above the first measure. It is not above, it is on (One behind another).

Any other comparison chart that we build in Tableau needs the same scale for comparison. But Dual Axis (The word itself is very intuitive – 2 Axes: one for each measure) allows for the use of different measures. In many cases, Dual Axis is used for Time Series. Some common examples are: High and Low Temperature fluctuations across the Month; Open and Close Stock values plotted across the year on weekly basis; Revenue and Percentage returns for every day etc.

To follow best practices (* Not rules or regulations, only practices), it is good to have colors and labels to differentiate the measures. In many situations, the bigger measure goes on the back and the smaller measure is plotted in the front. Also, one thing to note, in many situations, developers build a Dual Axis with Bar Chart on the back and Line Chart in the front.

To demonstrate this, we will be using our Sample – Superstore data set with the Orders table.

For most other multi-measure comparison charts (Stacked Bar for measures, Side by Side Bar for measures, Bar in Bar for measures), we need the special fields Measure Names and Measure Values. But Dual Axis neither needs Measure Names nor Measure Values.

But for our examples, we will only use Measure Names. This helps in creating a legend which otherwise becomes difficult for identification.

There are different ways to build a Dual Axis chart. Three different methods will be shown here.

Method 1

This is the most detailed step-by-step approach to build a Dual Axis chart. For this method, we will use the Order Date field and 2 comparable measures – Sales and Profit

1) Use Order Date as Month (Discrete) on Columns.

We are using Columns because Time Series is typically represented on X axis. It is always a good practice to first start with an aggregated Measure and then slice and dice using a Dimension. But in this case, we are starting with the Date field.

2) Use Sales as Sum aggregation on Rows. (We need a vertical axis chart). Since it is a Date field, Tableau will automatically create a Line chart.

3) Use Profit as Sum aggregation also on Rows to the right of Sales field. The idea is, the field on the left goes behind the measure on the right.

At this point of time, the Marks shelf has split into 3 components (One for each measure and one called “All”. The “All” segment controls the entire view.

4) Use the second measure to create Dual Axis. Automatically both the charts are combined into one. At the same time, both the measures on Rows seem like a combined field. We also have Measure Names field controlling the colors.

5) Manage Marks for each measure. We need to change the chart type for each of the Marks segments: Bar chart and Line chart.

6) Last and very important step. Most of us forget this step. At this point it looks like the Profit values are higher than Sales values. It is only because of the different axes.

So, if using comparable measures, we need to Synchronize the second axis.

Method 2

This is a simpler-faster 3 step approach. For this method, to try different measures, we will use Order Date field and 2 non – comparable measures – Sales and Quantity. Sales is a Currency field and Quantity is a Units field

1) Use Order Date as Month (Discrete) on Columns.

2) Use Sales as Sum aggregation on Rows.

3) Use another measure (in this Quantity as Sum aggregation) opposite to the existing measure axis.

Here, Sales is on Y axis on the left-hand side. So, we need Quantity field on the right-hand side. At this point, we need to identify a **Dotted Line or Dashed Line visual cue.

At this point, Tableau will automatically create a Dual Axis. Some extra steps if needed can be performed.

4) Manage Marks for each measure

5) If required, we can Synchronize the second axis.

In this case, Sales is converted to a Bar chart. But Synchronization cannot be done (or some say should not be done) for two reasons: one being different scales, other being different data types. (Sales is a decimal and Quantity is an integer)

If we force the Synchronization by changing data types, it results in a chart which is not appreciated. For best practices, it is better to leave it as is.

So, without Synchronization, we arrive at this.

Method 3

Here we will take the quickest and easiest route using SHOW ME

1) Keep the SHOW ME drop-down open

2) Use the CTRL key on the keyboard and select (click) at least 1 Date and at least 2 Measures together. In this case, the Order Date field, the Profit field and the Quantity field.

3) Finally select the Dual Combination chart from SHOW ME. (6th Visualization on the 3rd Column)

Whatever we needed to achieve in steps is completed in just 4-5 clicks.

At this point, we notice that Tableau has converted the Date field into a Green colored Continuous field.

As discussed before, Dual Axis is typically built for Time Series. But Tableau gives us the flexibility to use any Dimension of choice when building it manually without using SHOW ME i.e. Show Me only accepts a Date field on X axis as a Continuous field.

At this point, if needed, we can change the chart type from Marks and Synchronize the second axis.

About the Author

A BI Analyst and Tableau trainer working for USEReady since 2015. Well versed in Data Analysis, ETL and Dashboard building across different business domains.

Vijai NarasimhaSenior Business Intelligence Analyst | USEReady