This tip comes from a comment from a viewer. They wanted to know how they could build a dual-axis chart based on a single dimension (e.g., Order Date) and a single measure (e.g., Sales). In this tip I show you how to compare the monthly sales for the current year (as a line) to the prior year (as a bar).

Before I get started, credit for the technique/trick in this blog post to Jonathan Drummey.  Follow Jonathan on Twitter or follow his new blog. 

Also, if you don’t use the Tableau Forums for getting answers to those things that have you stumped, then you’re missing out.  It’s very rare that I can’t either (1) find the answer or (2) get the answer by starting a discussion.

Now, consider that you have a stacked bar chart of sales by Product Container color-coded by Region.

Great! But it would be more useful to label the top of each bar with the totals sales like this.

Awesome!  I was banging my head trying to figure out how to make this work.  Adding totals to a stacked bar is straight forward, but the trick here is that the bars represent % of total, not the raw sales.

It seems so simple, but I was stumped.  Fortunately Jonathan was there to save the day, and I can’t believe how simple it was. 

Here’s how it’s done, starting with the stacked bar.

Step 1 – Create a calculated field with the value of one

Step 2 – Drag your new “One” measure onto the right side of the chart until you see the dotted lines (which indicate a dual axis), then drop it.

You now probably have an ugly chart like this:

Step 3 – Change the aggregation for the One measure from Sum to Average (right-click on the One pill, go down to Measure, then pick Average)

Step 4 – Right-click on the right axis and choose Synchronize Axis

Step 5 – Click on the carrot on the top-right of the Marks card and choose Multiple Mark Types

Step 6 – Scroll to the right on the Marks card until you get to SUM(Sales).  Change the mark type to Bar.

Step 7 – Scroll to the right again to get to AVG(One). 

1. Remove Region from the Color shelf
2. Set the color transparency to 0%
3. Change the mark type to Gantt Bar (Joe Mako suggested using Gantt Bar instead of Bar because it creates a line at the top and it will eliminate the chance that the user could select a hidden bar.)
4. Drag the Sales measure onto the Label shelf
5. Set the Label alignment to Top

You should now have a chart that looks like this:

Step 8 – Right-click on the right axis and uncheck Show Header

That’s it!  Pretty simple! (Now that I know how to do it.)

Download the workbook here.

I’ve been gone for a few weeks enjoying some much needed time off before changing jobs and moving to the west coast.  I’m hopping back into the blog saddle with a series of posts about different charts type, their strengths and weaknesses, when to/not to use them, etc. 

The posts will include step-by-step instructions for creating the charts in Tableau.  The instructions for many of these charts have been written before and I will reference the authors whenever I know about their work.

I will typically use a chart I’ve found on the internet in order to provide variety of examples and also to provide a forum for discussing the strengths and weaknesses of their chosen designs.

Let’s get right to it with this lollipop chart from The Washington Post (via Chart Porn):

I like the clean design, the use of simple colors, and the excellent use of data-ink ratio of this chart, but there are few issues that immediately stand out to me.  First though, what is a lollipop chart?

I couldn’t find an exact definition, but I think of a lollipop chart as a combination of a bar chart and a dot plot.  Lollipop charts are great for giving you a sense of both length (bars) and precision (dots). 

However, it only makes sense to use the stick of the lollipop when you’re range starts at zero. 

In the example above, the bars start at 60, therefore including the bars could mislead the reader into thinking the retirement age in Malta is five times lower than Austria. In this example, the bars should be removed, which turns the chart into a dot plot.

Another problem with this chart is that there is no particular rationale to the sort.  At first I thought it was ranked buy retirement age from youngest to oldest, but then Spain was listed before the United States.

For the purpose of the rest of this blog post, we’re going to focus on the retirement age only.

Taking the issues above into account, the data could be represented as a simple dot plot:

Or alternatively as a lollipop chart:

Hopefully at this point you understand when and why you would use a lollipop chart.  In the end, it’s basically a bar chart with a bit more emphasis on the exact value of the bar.  CAUTION: Don’t avoid a bar chart simply because a lollipop chart looks cute.  I would nearly always prefer a bar chart over a lollipop chart.

So now onto the instructions for how to build this chart.  (Credit to Andy Cotgreave, who back in his days at The Data Studio wrote similar instructions)

Step 1 – Drag the Country dimension onto the Rows shelf and the Retirement Age measure onto the Columns shelf.  The result is a bar chart.  Click the sort ascending button.

Step 2 – Drag the Retirement Age measure to the Columns shelf again.  The results is the same bar chart side-by-side.

Step 3 – Right-click on the 2nd Retirement Age measure on the Columns shelf and choose Dual Axis

Step 4 - Right-click on the 2nd Retirement Age measure on the Columns shelf and choose Synchronize Axis

Step 5 - Right-click on the 2nd Retirement Age measure on the Columns shelf and uncheck Show Header

Step 6 – Drag the right side of the chart to the left to shrink the view

Step 7 – On the Marks card, click on the triangle (a.k.a. carrot) on the upper-right and choose Multiple Mark Types

The Marks card should now show “All” at the top and there are now arrows for moving left and right through the measures on the Column shelf, e.g., the two Retirement Ages measures.

Step 8 – Click the right arrow on the Marks card once, then:

1. Change the format of the chart from Automatic to Bar
2. Move the Size slider all the way to the left to make the bars as small as possible
3. Remove the Measure Names field from the Color shelf
4. Change the color of the bar by clicking on the colored square.

Step 9 – Click the right arrow on the Marks card again to move to the 2nd Retirement Age field, then:

1. Change the format of the chart from Automatic to Circle
2. Remove the Measure Names field from the Color shelf
3. Drag the Retirement Age measure onto the Label shelf
4. Format the Label to the font color of your choice and set the horizontal alignment to Center to place the label in the middle of the circle
5. Change the color of the circle by clicking on the colored square
6. Resize the circle

Voila! You’re lollipop chart is complete.  After you practice these steps a few times, you’ll be able to build it in under one minute…guaranteed!

Download the Tableau Workbook here.