Creating Dual Axis Bullet Graphs with Tableau

One of the great tools for creating reports and dashboards is the bullet graph.  This type of graph was invented by one of the great experts in the data visualization field, Stephen Few.  If you are not familiar with his work then you should check it out at PerceptualEdge.com.  Bullet graphs are great tools for showing lots of information in a small amount of space.  They allow easy comparison between an actual measure and a target, for example actual revenue results to forecast revenue.  They are very useful for any performance measure against a target.

An easy way to show actuals to a target is a simple bar chart with the addition of a target line.  I would highly recommend using these, but this type of chart is really only useful if the target measure is identical across all performance measures.

Here’s an example of a simple bar chart with a target line.

The above example is a great way to show categories against a single target or average.  You can see other examples on our Blog (The Bureau of Labor Statistics Creates and Excellent Graph and Make Category Comparisons Much Easier with these redesigns).  These are all very well done target lines measuring the actual to some target.  However, in the above example it may not be reasonable to ever sell 16 units of oranges.  Let’s say for example the realistic target for oranges is only 9.  In this case the sales team for oranges may actually be superstars and we’re showing them as missing the target by more than any other category.  This is where the bullet graph comes in. The advantage of a bullet graph is that you can achieve the same visualization but at the same time it allows you to have different targets for the different performance measures.

For today I won’t go into the details on how to make them or how to read them.  There is an excellent description and step-by-step instructions on how to build these wonderful graphs at http://www.exceluser.com/explore/bullet.htm.  Instead, I want to provide an alternative improvement that might be useful to users in certain instances.

Typically the bullet graph is shown in one of two ways, values or percent.  Both of these measures can be useful depending on what result is being measured and compared.

Here’s an example of a bullet graph using actual values (using Microsoft Excel). 

Here’s an example of a bullet graph using a percent as the measure (Created using Microsoft Excel and a different data set).

In both cases you have an actual, the thin, dark blue bar.  That is then measured against a target, which is the dark blue vertical reference line.  The three bands of color in the bar are used as tolerance bands. I have found that in some cases you may want to be able to visualize the actual and target as both the value and the percent.  By adding a dual axis you get the benefit of both (Created using Tableau).

One additional note on bullet graphs. I have encountered two issues with using these graphs on business reports.

First, these graphs are not easy to make using the standard tools.  They are not native graph types in Microsoft Excel, so it requires a good bit of Excel skill to create these and requires a dataset for each graph, specifying the various components necessary to build the graph.  In other words, the person building the graph will not be able to simply highlight the data and create a bullet graph.  Tableau is one tool that offers the bullet graphs natively, but this also requires a bit of understanding of Tableau in order to create them.  However, Tableau does allow the user to create them without creating little data sets for each graph.  There is also an issue with creating a dual-axis bullet in Tableau that they are aware of.  This is outlined in the details of How to Create a Dual Axis Bullet Graph below.

More importantly, there is often confusion around the bullet graphs, probably because they aren’t seen or used as much as other graphs that users are used to seeing.  There is a learning curve to understanding what the graph means, how the actual vs. target is represented and more often what the color bands represent and how to interrupt it all.  However, in my experience, once the users understand them they seem to adopt them relatively easy. This is probably true for any new graph type that has been created over the years.

In my opinion, neither of these issues are insurmountable.  There are enough ways to create these graphs, either by using templates, add-ins, or different software platforms, and once you build a few of them it will get easier and easier.  As it relates to teaching people how to read them, I think the benefit of taking the extra time to educate the user on what they are looking at outweighs the complexity of the graph, both in the creation of it and the user’s comprehension of it.  It may not be a quick understanding, but in this case I think it’s worth the trouble.

However, if you are creating a report that will be read by many and there is no way to explain the graph then it may be best to choose a different way of presenting the data. As an example, in some cases the bands are not necessary to tell the story.  If the goal is to simply show Actual vs. Target then having a single color bar may work just fine (or maybe no shading at all).  In this case it’s simply a bar graph with a reference line for the target. Add a label or two and anyone should be able to decipher this.

Special thanks to Kristofer Still for creating the dual axis bullet graph in Tableau and detailing the instructions below.

How to Create a Dual Axis Bullet Graph in Tableau

You need to start with a set of data with actuals and a target.  In this example the target is a monthly sales quota of widgets and we compare this with sales to date.

First you create a calculated field for the ratio of sales to date to your goal.  This creates your percentage for your first axis. (You could just as easily calculate the percentage as a field in your data source and this might be preferable if you are creating multiple bullets for many targets.)

From there the easiest way to get a bullet graph in Tableau is to use the show me menu.  To do this select your sales to date and sales goal fields and select bullet graph from the show me menu.  You now have a single axis bullet.

Now we have to do some trickery to get Tableau to give us the second axis.

First you drop the percentage field to the Columns tray.  This will create two side by side bar charts.

Next you drag the percent axis and drop it on top of your bullet graph.

Now that we have the dual-axes there are some additional formatting steps to clean things up:

You want to change the color palette to one of the single gradient palettes and make the percent of goal measure a darker shade and sales goal a lighter shade.

You should also change the formatting of the top axis to percentage.

There is also a bug in Tableau that you will notice.  The reference line for the goal of 10,000 units doesn’t cross at 100% of your top axis.  Tableau provided me with a fix for this.  If you set the axis ranges proportional to one another then they will line up.

So in this case if you extend the upper end of the range of the percentage axis to 1.05 this will cause things to line up.  Be careful, though, this axis is now fixed and won’t automatically update if you place the bullet on a dashboard.  This is less than ideal, but unless you have wild swings in the magnitude of your measures you should be fine.

Your final bullet will look something like this: