Exercise: Dual-Axis Combination Chart

SUM of Profit versus Average Quantity by Category and Year

In this self-guided exercise, you will rebuild a dual-axis combination chart. One axis will display profit with a mark type of bar and the opposite axis will display average quantity with a mark type of line; both axes will be broken down by the Category and Order Date dimensions.

In this exercise, you’re going to be recreating this dual-axis combination chart. So a couple of hints and things about this one. What I would do is build one side of the chart at a time. So I would start with– it’s a simple breakdown. It’s a bar chart profit by year of order date. I’d start there, then I would add your second axis for average quantity, convert it to a dual-axis combination chart. So you’re going to use two different mark types. Once you’ve got the foundation, you can break it down further by the category dimension. Pause the video and take a shot at that, and I’ll come back soon as you hit play, and I’ll build this for you.

All right, so like I mentioned in the hints, I like to break things down into smaller elements. It makes it easier to build things in Tableau. So I would start like I always do– rule of thumb number one. I start with my measure, then I add context to that measure to get insight. So I’m just going to start with my profit measure by double clicking on it. I’m then going to double click on Order Date to break down that profit measure by year of order date. I’m going to change the mark type to bar. And there’s the left side of the chart. So far, so good– that was pretty easy to do.

I’ve showed you in a previous video two ways to make a dual-axis combination chart. One was to– so our second measure is quantity. But it has an aggregation of something other than the default. So it’s average instead of sum. The ways that I showed you previously to make this is you can add your measure to the rows shelf. I’m right clicking this time because I want to choose a different aggregation other than sum. So I’m right clicking, when I let go, I can choose average instead of sum.

This is what I showed you previously. Two ways from here– you can click into the second pill and click dual-axis. Or you can hover over the second axis, grab this green triangle, and drag to the right. I’m going to show you one more way to do it, it’s to skip one step. I’m going to kind of cut out the middleman here. I’m going to right click on quantity and drag it straight to the other axis. When that dashed line appears, that’s Tableau telling me where it’s going to drop that axis. I’ll let go, choose Average again, click OK.

At this point, we have a dual-axis bar chart. It’s not a dual-axis combination chart because that name comes from having a combination of mark types. But because we’ve got two measures on the rows shelf, it’s very easy to change the mark types of these individually. So I’ll navigate to the marks shelf for the average quantity measure and change this dropdown from bar to line. Now, we’ve got a dual axis combination chart. So there’s the foundation. From here, I can break this down further by category. I’ll put category on the columns shelf and that was essentially the exercise.

But I want to point out just a couple of things. One is a best practice technique and one is also a tip for you to improve the formatting and save a little bit of real estate. So the first thing I want to point out is this chart type is fine as is, but if we were to change the order of these pills on the columns shelf– so if I were to put category second after year, notice that this line is now connecting dots between things that are not connected. It’s implying a relationship. So, for example, in 2016, this looks like a downward trend but that is not what that line represents. It’s simply connecting the dots between furniture, office supplies, and technology.

So be careful here. At a minimum, if you were in a similar situation, I would change the mark type for that second axis to circle instead of line. I’m going to undo that and undo one more time to get us back to the beginning. And I’m going show you a little formatting trick. I’m not always a big fan of the color legends in Tableau, mainly because there’s not a lot of formatting flexibility. You end up with these small squares. What you see is what you get.

Instead of having the color legend represent the color or tell the end user what the colors mean, sometimes I’d like to add those as the axis title. So the axis titles can double as color legends. It’s fairly easy to achieve. You right click on each axis and click Format. One of the formatting options for the axis is the title. Click this drop down, and you can choose the color of that measure. So I’m looking for orange. I could choose orange and just eyeball it, but I could be very precise if I wanted to by clicking More Colors and then clicking pick screen color. And I can pick the exact color orange being used in the color legend, click OK.

Maybe I’ll make the title a little bit bigger and bold. I’ll replicate those steps on the right axis by right clicking and clicking Format. I will choose a different color for the title here. I’ll do you pick a screen color, choose the exact blue, click OK, make it a little bit bigger, and make it bold.

This has been Ryan with Playfair Data TV – thanks for watching!