Day 18: Waterfall Chart

Today we'll look at another out of the box visualization - the Waterfall Chart.  This one is pretty well documented, but we're going to look at it in a different way than you may have before, so buckle up - it should be a good ride.

Learn About Tableau

"How did we get here?"  It's a great question.  Back on Day 10 we talked about big numbers, how sometimes there's only one number that matters.  Today we're going to take the opposite approach to deliver on the question - "It's great that that's my total, but how did we get there?".  A waterfall chart is one of the best ways to unpack a large number and to break it down into digestible bites - maybe by organizational unit (very common) or by time period (also common, but less typically for a waterfall chart).  Today we'll show examples of both, and dig into the nuts and bolts of how to look at this by organizational unit.  By showing each individual unit, it's much easier to get a sense of comparison and maintain context with the data (ie "Office Machines only had a $7k profit in the South Region compared to $101k in the Central Region, but total profit in the South was much less than Central so it's not as bad as it sounds...".  A waterfall chart can help visually give that insight (a particularly good choice for showing Profit by different slices).  I thought it was helpful to see the end result before we started yesterday, so before we get going, here's where we're headed:

 I've been speaking a lot of Spanish a work recently, so in that spirit - Vamos por Superstore Data (aka we're going to use the Superstore data set again, so go ahead and open it up)!

This is a pretty cool trick and actually rather simple.  Think about what's happening in the visualization - the total creeps up with each slice as a running sum, but only the sum of that slice is shown as the size of that bar.  This is how it works in general.  Today we're going to look at Profit broken down by Department and then Catagory.  

To start off, go ahead and change the mark type to a Gantt Chart.  Toss Department on the Columns shelf and Profit on the Rows shelf.  You should be here:

Interesting.  Now here comes the trick in two parts.  The first part is that, per the way we described it above, this needs to be a running sum of Profit so that we can see how each piece impacts the overall total. So go ahead and right click Profit and select Quick Table Calculation>>Running Total.  Ok, now we need to create the size of each slice of Profit, so intuitively we are going to throw Profit on the size shelf.  Hold on there cowboy.  If we did that we'd get this non-sense:

I don't even know what that is, except all kinds of wrong.  Rather - and here's the second part of the trick - we need the size of Profit to fill downward not update (as it's doing above).  Therefore we're going to create a calculated field called "Profit - Negative" with this formula:
SUM( [Profit] )*-1

Now if we place this new calculated field on the size shelf we go this much more intuitive visualization:

Each piece picks up where the other left off.  It's pretty cool.  So you've actually already completed creating a waterfall chart (ta-da!), but let's take it the extra mile and do a bit more.  First I want to see the total so on the top go to Analysis>>Totals>>Show Row Grand Total (we know we should select Rows because our measure is on the Rows Shelf).  Next on the Columns shelf, Department is part of a hierarchy.  Click the plus sign (+) to see the next level of detail, which is Catagory.  Now it's starting to get interesting:

I want to be able to quickly see if something is profitable, so now I will use profit and drag it to the colors shelf.  Since rules a made to be broken, I'll stick with this red-green diverging palette (Whaaaa? Christmas in April?), but I don't want this gradient - either be green or be red, but be all in!  In order to do this simply, click on the color legend>>Edit Colors.  Check the box that says Stepped Color and take the number down to 2.  Now click advanced on the bottom right and check the box next to the word "Center" and make sure that number is 0.  Everything looks like this:

Two more things - put Profit on the Text Shelf and add Region to the Rows Shelf (it's a more detailed view).  A little formatting here and there and...

Look at you!  Making waterfall charts!  Water fallin' out of control!  For fun I've also included an example of doing a waterfall chart using months of the year.  It's another interesting example (see below - What's up with Q1?):

Learn About Tableau

We that's it.  18 down.  We'll be here all weekend.  Tell you're friends! Thanks for hanging out!

Nelson