By combining table calculations, Gantt marks, and editing calculations in the flow of your analysis, you can create an engaging chart type that illustrates the contribution of individual dimension members to a cumulative total.
Hi. This is Ryan with Playfair Data TV, and in this video, I’m going to explain to you how to build a waterfall chart. Waterfall charts are a really engaging way to show you how your individual dimension members are building up to a running total.
To show you this one, I’m going to start with the measure of Profit, and I’m going to break it up by the Sub-Category Dimension. You don’t have to sort this chart, but it’s similar to why you might want to sort a bar chart. It just gives you a little bit more precision. It helps you compare the performance of the individual dimension members when this is sorted.
So just like I typically sort a bar chart, I also typically sort a waterfall chart. So the next thing I’ll do is just sort these in descending order by clicking this button under the Window option in the top navigation.
There’s two kind of special things about a waterfall chart. They’re built with a table calculation and a special mark type called Gantt Bar. The first thing we’ll do is add a table calculation that computes the running total of profit. Table calculations are added to measures, and there is only one measure on the view right now, Profit.
So I’ll click into that one measure and see if I could find a hint in here about how to add a table calculation. There’s two things related to table calculations. I can add the table calculation manually, or if I hover over Quick Table Calculation, these are some commonly-used, preset table calculations that allow you to add this functionality without having to know how to set up a table calculation or write the calculation out.
And in fact, the very first one is the one that we need, Running Total. So I’ll click on that. You can now see instead of just showing the raw sales or raw profit amount per sub-category, we’re seeing a running total from left to right.
The next thing I’m going to do is change the mark type from Bar to Gantt Bar, which you can do by choosing this dropdown or clicking on this dropdown on the Marks Shelf. And I’ll choose Gantt Bar. For now, what this is doing is just putting a dash at the top of where each bar used to be.
Gantt bars need to be sized by something in order to get them to work. Instead of just showing a dash, we need to size these Gantt marks by something so that they expand vertically. I’m going to size those Gantt bars by the Profit measure by left clicking Profit and dragging it to the Size Marks Card.
You’ll now start to see these blocks come together. Some people call this a Mario Chart because of the old video game where you jump and hit the blocks. So we can see those blocks coming together, but they’re not quite right. They’re not quite lined up.
What’s happening is the bottom of each of the blocks was the top of where the bars used to be, and when we sized these blocks by the Profit measure, they expanded vertically. But they don’t necessarily line up where they should be like you would expect to see with a waterfall chart.
The trick to getting this to work is, we need the size of those profit values to be going the other direction. We need those to go down. That would make all these blocks line up. In order to make those bars go down, one way to do that is to multiply those profit values by negative 1. That would make each profit value negative and the bar would point down instead of up.
I could set up a calculated field minus 1 times profit. I could also write in a slightly more elegant way by just saying negative sign profit. That would be the same thing as minus 1 times profit. But the best possible way to do it is just to modify that calculation in the flow of our analysis, which you can do by just double clicking on that Profit measure. I always say this gives you an X-ray vision into that measure, and I’ll just navigate to the beginning of that measure and add a negative sign.
So I actually just made a calculated field right there, but I didn’t have to get out of the flow of my analysis. I just double clicked on the Profit measure that was determining the size of the marks, and I added a negative sign at the beginning, which is the same thing as saying minus 1 times SUM of Profit.
Click the Enter key, and now you can see that the bars are going down, and it’s making them all line up.
So that’s essentially it for a waterfall chart. You could stop there, but just give you a couple more ways to clean this up a little bit that are optional, I could also color those marks by Profit. I’ll put Profit on color. And this one I don’t want to multiply by negative 1. This is already doing what I want it to do. The higher the profit, the darker the blue; the lower the profit, the darker the orange. So I’ll call that good.
And then on the Analytics pane, another option for you is you could drag the totals onto the view if you wanted to see where you ended up across all– in this case, 17 sub-categories.
This has been Ryan with Playfair Data TV – thanks for watching!