Day 2: Hidden Reference Line Totals

It was Aristotle who famously said: “The whole is greater than the sum of its parts.”  When you look at your data, you better hope that's not true :).  But in Tableau sometimes that can be tricky to validate.  So for today we're going to see how to take a sliced up bar chart and show the total of each of the bars.  This one is probably filed under "There has to be a way to do this, but I just don't know what it is".  I have good news for you!  Here we go...

Learn About Tableau

Today (and for many of our days together) we'll be connection to everyone's favorite - the Superstore Excel dataset that comes prepackaged with Tableau.  We'll do this because it ensures that we're all starting from pretty much the same place.  So go ahead and connect to the data and we'll get started.

We're going to do a basic example that illustrates this trick.  Take "Region" and place it on the rows shelf, and "Sales" on the Columns shelf.  Next add "Department" on the Colors shelf, and finally add "Sales" again to the text shelf.  You're viz should look something like this:

Now here's the fun part.  Without adding the three Department sales together, what's the sum of sales for each region? Sure you could take Department off the color shelf and see the total again, but now you've lost the breakdown we're currently looking at.  That's a non-starter, as I want to present more information, not less.

So here's the trick.  Right click on the X-Axis and select "Add reference line, band or box".  You should now be looking at a popup box that looks like this:

Here's the goal.  Rather than creating an actual reference "line" we're going to make a hidden line with visible text, and the text will show the total sum of Sales for each of the regions.  Cool huh?

To do this enter the settings you see above.  
Click "Per Cell" 
Value: "Sum(Sales)"
Aggregation: "Sum"
Label: "Value" 
Line: "None" (this is done so that the line doesn't show, only the text showing the value of the total sum)

When you're done you'll now see the totals at the end of each bar.  It's likely that you'll need to do a bit more formating to get the text looking the way you want it.  So right click one of the reference line numbers, and select "Format".  My preference is to have right justification in the middle of the bar, but do as you see fit.  You could make the text larger, or change the units (maybe set it to $ Millions).  

And the final product looks something like this:

And there you have it!  Simple but helpful, just as any good hack should be.

Learn About Tableau

I hope you found this one helpful.  If so, I'd love to hear about it.  We're just getting started, so come back soon!  

Many thanks - 
Nelson