You will learn how to make diverging bar / tornado / pyramid charts by (1) reversing axis scales or (2) creating a calculated field. Ryan also shares when not to use this chart type and pros and cons of both approaches.
Hi, this is Ryan with Playfair Data TV. And in this video, I’m going to show you two different ways to make a diverging bar chart in Tableau. By the end of this video you’ll be able to make a chart that looks similar to what you see on the screen here. This particular diverging bar chart evaluates the interconference records between the National Football League’s AFC conference and NFC conference. You can think of this as a tug of war.
You can see at the top there it says, wins above .500 for the AFC are going left, and wins above .500 for the NFC are going right. Well, in sports leagues, .500 represents a tie. So in this case, each block to the left represents one win above a tie for the AFC. And each block to the right represents one win above a tie for the NFC. The key distinction with this chart type is both of those values are positive, but I’m making them diverging for a couple of reasons.
One, is aesthetics. I think this is a very nicely designed chart, but it also has the advantage of really being two bar charts in one. You can read a bar chart on the left or a bar chart on the right even when the values are both positive and in both directions. That’s what I’m going to show you how to do here today. Before I show you how to do that, I’m going to show you when you do not need to listen to the rest of this video and use these special steps to make a diverging bar chart.
First of all, if you actually have negative values in your data set for the chart type, Tableau is going to do that for you automatically. Here’s an example of that on the screen. I’m using the measure of Profit, which in the Sample-Superstore dataset has both negative and positive values. This navy blue line in the middle, that’s the zero line.
So anything that’s going to the left is a negative value, anything going to the right is a positive value. I’ve then got Region on the Rows Shelf, so we see the four different regions. I’ve got Sub-Category on the Detail Marks Card, that’s what each of those blocks in this stacked bar chart represent. And then they are colored by profit. You see that orange warning color if It’s going left. And a greenish blue color if it’s positive and going right.
In this case, Tableau made a diverging bar chart for me right out of the box. It just took the negative values, pushed them to the left. Took the positive values, pushed them to the right. But in that example that I just shared– just one of infinite examples– the NFL Interconference records, both of the values are positive.
So if I were to stack those on top of each other, everything would go to the right. I would lose that aesthetic and that practical example where I get to look at a bar chart that’s also going to the left. So that’s what I’m going to show you how to do here. I’m going to show you two different ways to do it. So that we can follow along using the Sample-Superstore dataset, I’m going to use some fields that exist in that dataset.
I have to admit there’s not a great example, but the way that you can apply what I’m showing you to a real world scenario is you could think of this as survey result data. Where let’s say we’ve got a series of questions and the answers range from one to five. Well, all of those values one to five are positive, but do we really want to get a one or two? No, those are really negative.
You could take those values of one and two and move them to the left, so that you could read a bar chart on the left for your negative values. Have your scores of three, four, and five go to the right so that you’ve got a second diverging bar chart where you can read it on the right side. To create a similar example using what we have to work with in the Sample-Superstore dataset, I’m going to use Sales as my measure and drag that to the Columns Shelf. All sales values are positive, so they will move to the right of zero.
I’m going to put Category onto the Rows Shelf so that those sales values are broken down by category. I’ll make this a little bit taller by making this fit the entire view. And then I’m going to drag Region to Color so that we can see four different stacks show up in this chart. And for this example, again, I’m admitting I don’t have the best example here, but I’m going to pick two of these Region dimension members to move to the left.
Before I do that, I’m also going to make the size of this a little bit skinnier just to make this look a little bit nicer. So I’m just going to randomly pick– I’ll just use the first two, Central and East. The blue block and the orange block. And in this first approach I’m going to move those to the left by first filtering the entire chart to just those two regions. So I’ll drag Region to the Filters Shelf, keep only Central and East, and click OK.
And the trick in this first approach is I’m simply going to reverse the axis for the chart that includes only those two dimension members that I want to move to the left. To reverse an axis, you right-click on the axis and click Edit Axis. And then you just check this box for Reversed. If I change that scale, notice that now the zero is on the right side. So I’ve just moved those two bars to the left.
To create the right side, I would create the same chart. So of course, I’m trying to show you this from scratch. You could just simply duplicate the sheet as well. But to show you how we got here for the right side, I’m going to replicate the steps. I’m going to put Sales on the Columns Shelf. And then we had Category on the Rows Shelf. And Region on the Color Marks Card. Make that taller, make the size of those marks a little bit better, so it looks nicer.
For the right side I’m keeping the remaining two dimension members, so South and West. So I’ll drag Region to the Filters Shelf. Keep only South and West. Click OK, and that’s already the right side of the chart, because we wanted those to go to the right. So let’s take one more look on the left side. Central and East are moving to the left. The zero is on the right.
For the right side, South and West start on the left side, so here’s zero. And then they are pushed out to the right. From here all I have to do is put these on a dashboard and make them line up together. So I will start a new dashboard. And I’ll make these floating. And put the left side onto the view. Maybe make these 400 by 400.
So there’s my left side. And then do the same thing from my right side. Make that the same size, 400 by 400. So this is the easy way to do it. I would then line these up– that’s at 217 pixels down. Maybe hide some of these headers just to clean this up a little bit. Get rid of my color legend.
And there’s a couple more things I could do with this. One thing is I’d probably center these labels on one side. And I could hide these, but here’s one of the pitfalls with approach number one. Notice, right now they’re on a similar scale because they’re including the headers on both sides.
If I were to hide these headers by right-clicking and deselecting header, that left side looks like it’s being pushed out further to the left. It’s simply because it’s now got more room, and it’s because these are both 400 pixels wide, it’s extending the scale. Some of the scale on the right side is taken up by the headers.
So that’s one of the gotchas with this approach. It’s practical, it’s easy, but it is easy to fall into this trap of– not on purpose– but misleading your user by having different scales on both the left and the right side. One way to kind of fix that is I could just make these headers blend in to the background by changing the font color.
So now they’re still on a similar scale, the headers are just hidden there. But this is where approach two comes in. So I’m going to show you a way to get these onto one sheet. There’s a couple benefits to that. One benefit is it’s typically best practice to try to consolidate sheets. That helps with efficiency, it helps with managing the file when you have fewer sheets to deal with. But probably a more practical and better reason to use approach number two is it’s going to put everything on one sheet so that they share the same scale.
So I’m going to go in and make a similar chart, but this time I’m going to make everything on a single sheet. The main thing that we need to make this work is a calculated field that classifies our Central and East regions as negative values so that they move to the left. So Central and East, the orange and the blue are going to go to the left.
To do that, I’m going to make a calculated field out of the Sales measure by right-clicking on it, hovering over Create, and click Calculated Field. I’ll call this Diverging Sales. And for this one there are only two outcomes. When the region is either Central or East, I want the values to move to the left. For the other two values, I want them to move to the right. So those are the two outcomes.
Anytime that is the case, I like to use this Immediate IF statement– and I’ll explain what this does in just a moment. But the formula is going to be, so Immediate IF the Region equals Central or the Region equals East, comma, I’m going to multiply the Sales values by negative one, which you can do by just typing a negative sign before the measure.
Let me extend this a little bit for you. Make it a little bit bigger so we can see it. After Sales I type another comma, and then Sales again, but this one will be positive. Then close parentheses. So if you’ve never seen this, anytime you see something blue in a calculated field dialog box, you can click on it. It will look it up over here in this little data dictionary.
But what this is saying is, when this criterion is true– so either the Region equals Central or the Region equals East– I want to display negative Sales. Otherwise, anytime this criterion is false– so everything else– I want to display the positive Sales. I’m going to click OK. That’s going to be what I put onto the Columns Shelf. We’re still breaking this down by the Category dimension on Rows.
I’ll make this bigger so that we can see it. And what we’re missing right now is the color for Region. Tableau right now can’t do the proper attribution, or classification rather, because it doesn’t have that level of detail on the view. So right now what we’re seeing is everything moving to the left, because those two dimension members of Central and East are overpowering everything else and it’s pushing everything to be negative.
But if I put the Region dimension onto Color, that field becomes part of the visualizations level of detail. And now you can see how this is working. Make this a little bit skinnier one more time. Because of our calculated field, any time the Region equals Central or East, the sales values, even though they’re positive, they’re multiplied by negative one. That moves them to the left. So my orange bar and blue bars are going left.
On the right side, I’ve got South and West. So my turquoise and my red are moving to the right. The only other pitfall with this– I show you two approaches because there are always multiple ways to do the same thing in Tableau and they all tend to have their own pros and cons.
One negative with this one is if– and no pun intended– if you actually have negative values, a negative times a negative is going to make that positive and it’s going to flip it the other way. So depending on your use case, depending on your dataset, there were two different ways to make a diverging bar chart in Tableau.
I’m Ryan with Playfair Data TV – thanks for watching!