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).
- Remove Region from the Color shelf
- Set the color transparency to 0%
- 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.)
- Drag the Sales measure onto the Label shelf
- 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.
Nice, and thanks for the hat tip!
ReplyDeleteThese colors are not distinguishable to close to 10% of the male population. Run it through www. vischeck.com or similar program.
ReplyDeleteYes, I know that the red/green combo isn't the best. I was using Tableau's defaults, but to your point, while I was making the point about a technique in this post, I should still have paid attention to the colors. Thank you!
ReplyDeleteI like this trick. This is something I can definitely put to good use.
ReplyDeleteGood post. I have a twist on it that I'm trying to figure out. I want the label above the bars to not be the sum of the units making up th bars but a totally different field. My bars are # of customers getting an offer each month. I want the number above it to be what % of the total customer base that represents. Unfortunately, I am also using the second axis for a line graph that shows total # of offers given. So, my two axis are both whole #s while I want the number above the bar to be a %.
ReplyDeleteGot any ideas?
thanks!
How does this work for you?
ReplyDeletehttp://public.tableausoftware.com/views/Bar-LineDualAxiswith3rdmeasureaslabel/Sheet3?:embed=y
The bars are sales, the lines are profit and the labels of the bars are % of total customers.
Thanks for the response. I don't think that quite does it, though. Putting it in terms of the example you presented in this post....how would you have gotten the independent measure on top of the stacked bar chart if you were already using the second axis to display another variable?
ReplyDeleteThe link I posted above does indeed use an independent measure on top of the bar. I'd suggest you download the workbook and see how I did it.
ReplyDeleteThanks. Where I ran into trouble when I downloaded and played with your file was when I turned the bars into stacked bars by dragging customer segment onto the color shelf of Sales. This caused the independent label above the bars to disappear.
ReplyDeletethanks
How does this work for you? It's as close as I can get. I did it via a reference line on the secondary axis.
ReplyDeleteChart