Data Viz Done Right

July 26, 2012

Tableau Tip: Adding totals of a 2nd measure on top of stacked bar charts

18 comments

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.

image

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

image

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

image

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:

image

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.

image

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

image

You should now have a chart that looks like this:

image

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.)

image

Download the workbook here.

18 comments :

  1. Nice, and thanks for the hat tip!

    ReplyDelete
  2. These colors are not distinguishable to close to 10% of the male population. Run it through www. vischeck.com or similar program.

    ReplyDelete
  3. Yes, 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!

    ReplyDelete
  4. I like this trick. This is something I can definitely put to good use.

    ReplyDelete
  5. Good 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 %.

    Got any ideas?

    thanks!

    ReplyDelete
  6. How does this work for you?

    http://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.

    ReplyDelete
  7. 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?

    ReplyDelete
  8. The 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.

    ReplyDelete
  9. Thanks. 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.

    thanks

    ReplyDelete
  10. 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.

    Chart

    ReplyDelete
  11. Can this be updated for new version of Tableau? I can't get the final label (7.5) on the graph.

    ReplyDelete
    Replies
    1. Liam, try this http://vizwiz.blogspot.co.uk/2015/05/stackedbars.html

      Delete
    2. that link was ok for putting the labels at the top of the stacked bars, but didn't really help with having the total as percent. Are there any instructions for how to do "Add totals of a 2nd measure on top of stacked bar charts"
      in the latest version of tableau

      Delete
    3. Keith, as long as the measure is in your view, you can add it via a reference line and that should do the trick. Or you could use the technique I describe above and then put the % of total measure on the label shelf.

      Delete
  12. I am using Tableau 9. After Step 4 I did not find multiple on my top-right of marks card. How do I proceed?

    ReplyDelete
    Replies
    1. With Tableau 9, you can right-click on the axis and change the mark type.

      Delete
  13. click on carot ,on top right of marks card ,you mean where exactly ?

    ReplyDelete
  14. click on carot ,on top right of marks card ,you mean where exactly ?

    ReplyDelete