9/26/2022
How to Create a Secondary Axis in Tableau Without Duplicating Marks
This topic came up relating to a recent discussion on Twitter, and idea posted by Sam Parsons. The idea he posted is basically that we should have more control and flexibility with the X and Y Axis in Tableau. I completely agree, so please vote for this idea. One of the most frequently used “tricks” in Tableau is to duplicate your pill to make a dual-axis chart. This is a very popular trick, because historically, it was the only way to get two different mark types on the same canvas (see Layering Marks in Tableau which leverages Map Layers as another way to hack additional marks on the canvas – more than two mark types).
A lollipop chart in Tableau is a good example of how duplicating pills are used. To build a lollipop chart, you would duplicate your green pill, and set one mark type to a bar and the other mark type to a circle. One of the major downsides to doing this is that it doubles the number of marks on the canvas. One set of marks for the bars, and then another set of marks for the circles of the lollipop, but Tableau needs both of those marks to create the lollipop.
Another use case for the dual-axis is to move the X or Y axis (green pills) to the other side of the chart. Tableau doesn’t allow you to simply move the X-Axis from the bottom to the top, or the Y-Axis from the left to the right. Therefore, this trick is often used to create a dual-axis. This is what Sam Parsons was writing about.
A Dual-Axis Bar Chart with Duplicated Marks
Using the Superstore dataset:
Drag Sub-Category to Rows and Sales Columns
Control-Drag Sales to the right of the Sales on Columns to Duplicate the pill, or drag Sales a second time to Columns.
Right-click on the second Sales pill on Columns and select Dual-Axis
Right-Click on the X-Axis or Secondary X-Axis and select Syncronize
Set the All Marks card to Bars
You now have a bar chart with axis labels on the bottom and the top. You can’t hide the bottom axis, but you can double-click the x-axis, erase the title, and set the tick marks to none, and then the chart will look like this.
If you follow these steps, then you will notice that 17 Sub-Categories turned into 34 marks on the Tableau canvas (see the bottom left hand corner of Tableau for the number of marks). By duplicating the pill, the marks doubled.
A Dual-Axis Bar Chart without Duplicating Marks
Now let’s create the same chart without duplicating the marks. To do this, we will create a calculcated field. However, when you create a calculated field in Tableau, you are basically creating another column of data, depending on the calculation we use. For example, if you create a calculated field based on Sales then it will end up with the same result as duplicating Sales and the same number of marks. Instead, we need to create a calculated field that has a single value for a single row. In this case, a single Sub-Category that has some value, instead of a value for every Sub-Category. We will leverage a simple table calculation to do this.
Calculated Field: Secondary Axis
Formula: IF FIRST()=0 THEN AVG(0) ELSE NULL END
This calculation will simply assign a 0 for the First row in the partition. Everything else will be NULL. By doing this, we are only adding a single value, rather than a value for every row. The value doesn’t really matter, as long as it is in the range of the data in the view. In most cases, zero is probably safe, but you may need ot adjust based on the data and the view you are trying to achieve. Another example calculation could be IF SUM([Sales]) = WINDOW_SUM(SUM([Sales])) THEN SUM(Sales) ELSE NULL END. In the end, you want one value and the rest to be NULL.
Building the new chart:
Drag Sub-Category to Rows and Secondary Axis Columns
Drag Sales to the right of the Secondary Axis
Right-click on the Sales pill on Columns and select Dual-Axis
Right-Click on the X-Axis or Secondary X-Axis and select Syncronize
Set the All Marks card to Bars
You now have the same bar chart as before, but this time there are only 18 marks on the canvas. 17 marks of the original data, and a single mark to create the secondary axis.
Note – you will have NULL records, so you will need to Hide the Null Indicator, but you will not duplicate all of the marks in the view.
With a little formatting, you can even recreate an Economist-Style Bar Chart in Tableau.
Below is a sample Tableau workbook on Tableau Public: