July 26, 2012
Tableau Tip: Adding totals of a 2nd measure on top of stacked bar charts
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.