Our Certified Tableau Trainers are asked a myriad of questions each day. In this series, they share some answers.

From a Desktop II Intermediate class in Melbourne, Priyanka asked me the following question:

“In my chart, I want to show forecast and actual as a bullet graph. I’d like to label both values at the end of the chart, whether that’s the bar that’s exceeded goal or just bullet if the goal hasn’t been reached yet.”

This was an interesting question that I thought would benefit others to see a solution to. Let’s dig in!

Bullet Chart

We are using our Sample Superstore data to get the following view:

Bullet Graph Comparing 2014 Sales to Average Annual Sales

The key elements to a bullet graph are using the second measure, the goal, in two reference lines. Remember to put this measure on the detail button, so we can use it in our reference lines. The first is a reference line per cell as the bullet, a thick black line to serve as the finish line for the goal. The second is a distribution band with an indication of progress towards the bullet. I’ve set up my distributions as percentages: 50, 75 and 100 with a fill below gradient.

As a fun side note, I’m constantly telling my students that Level of Detail calculations pop up all over the place. The chart above is another example. I’ve calculated my goal measure as the average annual sales by sub-category using a fixed LOD, like so:

And here’s the calculation for our condition formatting:

Note, the LOD field is already aggregated inside of its calculation with AVG(). Now that we’ve got this starting block, let’s add a label to the end of each bar that includes both the actual sales, the average annual sales and the percentage progress.

Hidden Dual Axis

The way we are going to position are labels at the end of the bars, is to use a dual axis with a hidden bar. Remember, our goal is to put our labels at the far right of our chart, either next to the bar or to the bullet. I need to create a calculated field that will detect the larger measure, either Sales 2014 or the Average Sales by Year.

I’m going to use this to create a dual axis. I prefer to bring my second measure onto Columns, fix up my formatting on my marks cards and then combine into a dual axis after everything is perfect. Here are my steps:

1. Hardcode my mark type from Automatic to Bar on the All marks card
2. Hide the axis title on the Hidden Bar axis
3. Format the Hidden Bar axis to custom currency (no decimals, Ks)
4. Set the opacity of the Hidden Bar marks to 0%.

To show the hidden bar, I’ll add a very light border just so you can see it in the image. Remember, I want my hidden bars to be on the bottom of my dual axis, so I’ll add it to the left of Sales 2014.

Preparing Formatting Before Creating a Dual Axis Chart.

To create a dual axis, I’ll right click on my Hidden Bar field and select Dual Axis. Synchronize the axes and then hide the top axis that belongs to Sales 2014, so we’re only seeing a single axis at the bottom (the Hidden Bar axis). You can hide an axis by right clicking on the axis and unselecting Show Header.

Dual Axis with Hidden Second Axis

Looking at the chart above, you cannot tell that there are hidden bars beneath our Sales 2014 Measure. Now, it’s time to set up our labels on the Hidden Bar chart. I need to create another calculation for our percent of progress.

Drag the appropriate fields onto the Label button on the Hidden Bar marks card and then edit the label. Note, I put a few blank spaces before each line to space it away from the end of the bar when we look at it on the viz.

The final step is to turn off the tooltips using the All marks card. Here’s the final view:

I confess that it’s a bit busy. That’s why I made my bars so big and filtered down to a single category to keep it from being overwhelming. Regardless, it does solve the requirements as set forth for the visualization and that means mission accomplished!

Thanks, Priyanka!