Today we had an exciting lesson with Ruth learning to apply set actions and I really enjoyed it. I was fascinated how many cool things we are able to do using applications of set actions in Tableau. In this post I will show you one of the them which I found really interesting: creating drill down bar graphs. This topic came in the test Andy gave us last week and I actually let my final sheet blank for that question, but now I know the answer.
Let's get started!
Creating a drill down bar using Superstore Dataset Step-by-step
Background
Let’s look at an easy example below. We have Category, Sub-Category of the products and its sales in bars.
What we want to build now is a drill down bar graph for Category, so that when we click on each category it will show its Sub-Category. I beliebe there are many ways to do it, but I will use set actions, which we learned today. So here are the steps:
1.Create Set for Category
It is easy done by right click on Category -> Create -> Set. It is not important which values you tick on.
2. Add Set Action to the Worksheet
On you Menu Bar, choose Worksheet -> Action
Add Action -> Change Set Values…
Choose in the Target Set field the Set you just created. If there is an Error saying you’re missing a field in your worksheet, it’s probably because your Category field is not there.
3. Create a calculated field for the Sub-Category
This is for the ones which will show up when you choose a Category. By calculated field we say that if our Category Set is selected (IN), show us Sub-Category of that Category. Otherwise shows nothing.
4. Set up your worksheet
Now you can arrange your fields again like the beginning, please note that should should use your calculated Sub-Category instead (the one which is only showed up when you choose category, remember? In my screenshot it is SUB-CATEGORY). Drag also Category set in color field.
Right now there is nothing inside my SUB-CATEGORY field because there is no category chosen. Now if you click on a grey bar, it will break down into Sub-Category in blue colors.
4. (Optional) Some additional fancy stuffs
This is a pretty cool trick I learned today. If you want your chosen Category to be highlighed by putting a symbol in front of it like in the graphic below, you need to add a new calculated field. The logic works the same like in the Sub-Category calculation above, now we say that if I choose a Category, please appear this symbol, otherwise the that symbol.
This calculated field goes like this:
Note that the symbols here are Unicode shapes. You can add whatever you want, but make sure it is Unicode. There are plenty of Websites where you can get them when you search for "Unicode symbols".
Now we don’t want do show your original Category anymore because the new calculated one represents it perfectly. But you can’t also drag it out, because the set action works on that field. What you can do instead is right click on the “Category” on Rows, and untick “Show Header”. By this way your Category will be hiden.
Okay, so that’s it. I hope you learn something new like I did today.
Have fun!