Ryan demonstrates how to make stacked bar charts in Tableau, explains the limitations of this popular chart type, and most importantly, shows a user experience that helps get around those limitations. By the end of this tutorial, you’ll be able to make a stacked bar chart that is designed for a positive user experience.
Hi, this is Ryan with Playfair Data TV. And in this video, I’m going to show you how to make stacked bar charts in Tableau. But I’m also going to explain the limitations of this popular chart type, and most importantly, I’m going to show you a user-experience that helps you get around those limitations. By the end of this video, you’ll be able to make something similar to what you see on the screen here. This stacked bar chart looks at Sales by Year, by Region. It’s the Region dimension that is creating the stacks within each year, but notice as the user is clicking on different dimension members within that stack, that stack gets moved to the bottom. That’s what I’m going to show you how to do here today.
So over in Tableau Desktop first, we need to start by creating a stacked bar chart. To do so I’m just going to double click on Sales, which will add Sum of Sales to the Rows shelf, and I’m going to double click on the Order Date dimension, which will add Year of Order Date to the Columns shelf. Tableau tried to help us out a little bit here by automatically changing our mark type from Bar to Line because we are using an element of time with that Order Date dimension it was assuming that we were wanting to look at something over time so it created a line graph for us. We’re actually going to make a small change here and use this mark type dropdown to get us back to a bar. And then lastly, to create a stacked bar chart we need to add something to either Detail or Color. Let’s continue to use that region example. So I’m going to find the Region dimension under the location hierarchy and I will add it to Color just by dragging the Region dimension to the Color property of the Marks card.
And that was it for the first portion of this tutorial, we currently have a stacked bar chart. Very useful chart type for looking how the parts of a whole add up. Also, when you’re looking at it as a traditional bar chart, or only looking at the top marked performance, it’s very good at showing comparisons. However, the big limitation to this chart type, if we look back at the stacked bar, unless you are the dimension member on the very bottom, so in this case the West region in that kind of turquoise color, it’s very hard to do any other comparisons. Again, for the West region I can just glance at this, I can immediately see that 2021 was the best performer, 2020 was second best, 2018 was third, and 2019 was fourth and last.
However, it gets much harder with any of the other dimension members because they are stacked on top of each other. So if I were wanting to see how South did from year to year that’s much more difficult to see because each stack, including the South region, is inheriting the value below it. So by the time you get to the top of the stacked bar, and you see the blue for the Central region, just because the blue bar is the highest that doesn’t necessarily mean that it performed the best. Because it’s inheriting all the values below it.
Well, I’m going to show you a trick to get around that. And we’re going to allow our user to move the dimension member of interest to the bottom of the stacked bar chart. That way they get the best of both worlds. They can see a stacked bar chart and the parts to whole relationship. They can also, if they want to look at an individual dimension member and move it to the bottom, they can do that with just the click of a mouse.
To do so, this is actually a pretty simple trick, but really powerful. Just requires a couple of steps. The first thing we need to do is create a parameter out of the dimension members being used as the stacks. So in this case that means Region. Little shortcut to create a parameter out of a dimension that’s already in your data source, you can right-click directly on it from the Data pane, hover over Create, and click Parameter. It’s a nice little shortcut because as you can see, it automatically populates the list of allowable values with the dimension members from that dimension. So that is step one and that actually is the entire step. So I’ll go ahead and click OK.
The next thing we need to do is create a calculated field that if the dimension member matches the parameter that we created in the first step, we want to show a value that is higher than any other value currently in the view. This is going to help us sort it later on. The number is pretty arbitrary. It doesn’t mean much. It just needs to be higher than any other value in the data set.
Let me show you how to do that. I’m going to create a calculated field. And I’ll call this my Stacked Bar Sort. And this particular calculated field is only going to have two outcomes. And whenever that’s the case I try to simplify it by using what’s called the Immediate IF Statement, which is the letters IIF. And if you’re not familiar with this, any time you see something blue you can click on it. Click this little arrow, which will open a little data and calculated field and function dictionary, if you will. And it shows you what that’s doing and as well as how to use it in the syntax.
But how this works is whatever statement I type next, when that is true it’s going to display the first value after the first comma. Whenever that is false It’s going to display the second value. So for our statement I’m going to say, whenever the Region equals the Region Parameter, comma, what do I want to happen. This is where that value that I mentioned comes into play. We need to pick something that is higher than anything else currently on the view. Again, this is arbitrary so I’ll just go with a nice round one million. Then I have another comma. If the Region does not equal the Region Parameter I just want to display the original Sales values.
So to read this back on what this calculated field is doing, it’s looking at that Region equals Region Parameter statement. Whenever that is true it’s going to result in a value of one million. Whenever that is false it’s going to display or generate the original Sales value. This is what’s going to be used to sort my stacked bar chart. I’m going to go ahead and click OK.
We now need to integrate that stacked bar sort into the actual sort of the Regions. To do so I will click on the Region dimension, click Sort. By default it’s sorting it by Data source order in Ascending order. I’m going to change that to Field and go find my newly created Stacked Bar Sort. If we did this correctly, I should already be able to do a quick quality assurance check and see that Central move to the bottom.
Whenever you create a parameter in Tableau by default it takes the dimension members and it automatically puts the first allowable value as the current value. Because regions are in alphabetical order Central, which starts with a C, comes first so that’s the current value of the parameter. When I added this sort functionality, it moves Central to the bottom, even though it didn’t have the highest value. So I can already tell this is working.
One little aside on stacked bar charts before I show you how to put this into action, the sorting is a little bit confusing. Currently, a value of one million has been assigned to the Central region, but has been put on the bottom, even though this is in ascending order. To me, just logically, I would think if it’s the highest value we’d want it in descending. Start with the highest and then go to the lowest.
Well, stacked bar charts actually kind of work in reverse. Where if you look on the View here it’s an ascending order and it’s the South Region on top that has the smallest value, then East, then West, and then Central normally would not be the last value when it’s in ascending order, but because we assigned it a value of one million it has been moved to the bottom. I just wanted to point that out in case you’re confused on which of these two radio buttons to choose. You should just keep that in ascending order.
All right, so this functionality is already working for us. I can prove that by right-clicking on the Region Parameter and clicking Show Parameter. And if I choose something other than Central, we will see that that gets moved to the bottom. So if I choose East, the orange gets moved to the bottom. If I click South, South gets moved to the bottom. It’s already a nice functionality. That was a nice way to QA it, but we can make this work much better and much more intuitively for our end-user by using dashboard actions.
So to add a parameter dashboard action, I’m going to click, actually, this time I’ll click Worksheet. Normally, on these videos I show you how to add dashboard actions. This also works within individual worksheet. So let’s try that out. I’ll click Worksheet in the top navigation, click Actions. Here’s my dialog to add Actions, and I will choose a Change Parameter action. And we’re on the right sheet. This workbook only has one sheet. So Stacked Bar Chart. I’ll have it run on Select. We are targeting the Region Parameter, and we are passing the Region dimension. So that is all my settings. I will click OK. Click OK again. And now, instead of using the parameter control, I can just click directly on a dimension member on the view and see that those are getting moved to the bottom.
One other thing I don’t love about this chart, the default highlighting in Tableau is causing some extra highlight around, not only the dimension member that I’ve selected, so currently on the View I’ve got South, but it’s selecting the South region within a particular year that I clicked on. I’d rather that highlighting goes away. The highlighting is not really the key user-experience that I’m trying to create here. I’m just trying to simply move the stacked bars of interest to the bottom so that I can evaluate their trends over time.
So one more little trick in this video that I’ll share. If you create a calculated field, I always call this Don’t Highlight, and that is actually the whole formula as well. You actually, technically, could type in whatever you’d like here, but I just repeat the words, Don’t Highlight. It’s a string so I put it in quotation marks. Notice that my terminology, Don’t Highlight, it includes an apostrophe. So I have to use double tick marks instead of single tick marks, but that is the entire formula. I’ll click OK.
I’m going to add this to Detail so that it becomes part of the view, but notice the view doesn’t change at all. I lost my highlight because I’m no longer clicking on something. I’ll get that back. But the view and the values didn’t change, but that value of Don’t Highlight is now incorporated into the view, which means I can now use it within a dashboard action. So I will click Worksheet again, Actions, Add Action. This one is a highlight action so I’ll click Highlight. I’m on the right sheet. I still want this to run on Select.
But there is one specific targeting that I need to do here at the bottom. Instead of having it run on All Fields I’m going to choose the Selected Fields radio button and choose Don’t Highlight, click OK. Click OK again. Now, if I choose a different dimension member not only does it get moved to the bottom, I’ve lost my highlight. What that last little hack is doing is it’s causing Tableau to actually, it appears that it’s not highlighting anything, in reality, it’s actually highlighting everything. Because that Don’t Highlight value is on every single column of which column I click on, it’s highlighting it so that it’s in full color.
And what we’re left with is a very nice user-experience on these stacked bar charts where I can click on a certain dimension member and it moves to the bottom. That way I can see its individual trend. But if I’d like to use this as a traditional stacked bar chart where I can see the total performance over time that’s still intact.
This has been Ryan with Playfair Data TV. Thanks for watching.