Luke is back for another #WorkoutWednesday challenge. And for this week, he’s challenging us to recreate an actual real-world visual that Luke recently created for a client. It’s an interesting story, I encourage you to go read the intro. Let’s take a look at what we’re gonna do this week:

Cool! A waterfall chart. I don’t think I’ve actually made this chart type before so this will be a learning experience for all of us.

Recently, at TC18, I sat for the Desktop Certified Professional exam and while I was studying I learned that during the test Tableau let’s you use internet resources. I tell you this because while this is a project to challenge your Tableau technical skills, it bound to happen that you’re going to encounter a chart type you have NO IDEA how to recreate. And I’m here to remind you that IT’S OKAY to go look at other blog posts to help you a particular challenge. That’s what I had to do for this challenge.

Waterfall

Before I get to the waterfall, I need to figure out which dimension members we’re going to be visualizing. In the requirements for this challenge, Luke has instructed us to show the top 5 sub-categories based on sales and then group the rest of the sub-categories into an “Other” group. Additionally, I need to be able to let the user filter by region.

Cool, so the first thing I think of when I hear “top N” and “filter” together in the same sentence is SETS. What’s interesting that the #WorkoutWednesday crew (and the entire Tableau community) have been fixated on using #SetActions, so I found it interesting that this challenge involves a set as well.

Anywho, back to the task at hand. I’m going to create a Top N sub-category set based on the sum of sales.

Now, I’m going to use this in a calculated field to break out which sub-categories to show and which to group into the “Other” bucket.

IF [Top 5 Sub-categories] THEN [Sub-Category] ELSE “Other” END

I’m going to put this on columns and add [Region] to the filter card.

A quick sidebar regarding Tableau’s Order of Operations

Some might be tempted to the just throw [Region] on as a filter and just keep on moving along. But look at this:

I want you to notice where Top N filters happen and where Dimension filters happen. Do you see how Top N filters happen BEFORE Dimension filters? This means our Top 5 set will calculate BEFORE the [Region] (a dimension) filter is applied. This is not ideal and could result in erroneous results. In order to get this exactly right, I need a way to move our dimension filter up above the Top N filter. Tableau makes that really because any dimension filter can be changed to a Context filter by right-clicking on the pill on the filter shelf and selecting “Add to Context.” You are going to be given two visual cues that a filter is in context. 1) the pill itself will turn to a grey color and 2) it will be moved to the top of any additional filters. This is a really clever nugget that’s been built into Tableau’s visual UI.

Ok. so this is what my view looks like at this point.

Now I need to figure out how to go about to create a waterfall chart. In times like these, I have one resource that I turn to first. Tableau Zen Master, Ryan Sleeper and his outstanding blog. Sure enough, he’s got a post on how to create a waterfall chart. Be sure to check it out, his entire blog is spectacular.

Okay, so one thing he mentions is that I need to do a running sum of our measure, [Sales] in our case. Before I do that, I’m going to sort this descending by sales.

Now in order to get the size of the bars to get that waterfall effect, you see how the bars start at the sales amount? I essentially want to “fill down” and the way I do that is to take the inverse of our measure by putting a negative sign in front of the [Sales] measure.

– [Sales]

This is starting to come together but what I’d really like is for the “Other” group to be on the opposite side. This can be accomplished by putting our Set on columns and because it’s a discrete dimension it will put all the members IN the set to left and members OUT of the set to the right, which is exactly what I want.

Now to get the full bar that you see in Luke’s example? I need to add a Grand Total for rows and move it to the left

And I get something that looks like this

Cool beans! The majority of the technical requirements are complete and all that’s left now is some formatting. For the colors, luckily Luke gave us the hex codes that we’re supposed to use. THANK YOU! I don’t like having to guess or use a color picker. So now all I need to do is write a calculation that tests for [Profit Ratio] greater than 0.

[Profit Ratio] >= 0

And when I put that on color (and edit to the right hex codes I get

I am so close! Now for the dotted lines. My first instinct is that these are reference lines. So let’s go to the analytics pane of the data window and drop a reference line on our sales axis. I’ll set to each cell like this

which results in

Now I’ll do one for the “bridge” across the top. It’ll be nearly the as the first one but I’ll do across the entire table and set it the max

and that results in

Now for the bottom ones. That’s going to be a bit tricky because There’s no way for me to add a reference line to the bottom of Gantt bar, as our view is now. But I can do is write a calculation to find the bottom of each bar, use it as a dual axis and then add a reference line to that.

So what is the calculation? Well, let’s think about what we’ve built already and see if the answer can be deduced. Remember, I have the top of the Gantt bar as

RUNNING_SUM( SUM( [SALES] ) )

And the size of our bar is

– [Sales]

So if I want to know what the value is at the bottom of our, couldn’t I just smash those two calcs together?

RUNNING_SUM( SUM( [SALES] ) ) – [Sales]

Tableau is gonna throw an error because I need to aggregate both measures

RUNNING_SUM( SUM( [SALES] ) ) – SUM( [Sales] )

and when I put that on as a secondary axis I get

and if I look at the running sum of sales axis for “Other” and see where it stops at the bottom, it’s a little bit above $600k. Now, look where that second axis below and see where the “Other” mark is, it’s in the same spot, just a bit above 600k. And when I combine the axes and synchronize…

It looks like the same chart as before but now that we’ve got a second axis at the bottom, I can separately add a reference line to measure for each cell

I end up with…

HUZZAH! We’re in the home stretch. Just need labeling and formatting polish. Luckily, I don’t need to create anything new for the labels. I just need to understand how to use the alignment feature of the text/label card. On our primary axis, I’m going to put [Profit Ratio] on the text shelf of the mark card and edit the alignment to be Center/Bottom

And then for the secondary axis, if I use the same field that I have on columns for the text label and leave the alignment set as the default settings I get

YEAH BABY! Okay, we’ve reached the 99% complete. The only thing left is to polish things up a bit:

• remove borders
• remove ALL gridlines
• Remove all column headers
• remove the secondary axis header
• Remove tick marks on the primary axis
• Change the primary axis label
• Paint the background sheet a darker grey
• Don’t forget the tooltips
• Put this bad boy on a dashboard
• Add a title
• put the Region filter between the dashboard title and visual

And I’m done! Click the screenshot for the interactive version

GO FORTH AND VIZ!