Exercise: Create a Ranged Dot Plot in Tableau

Month over month sales and performance ranges by sub-category

In this self-guided exercise, you will make a dot plot showing month over month sales per sub-category in the Sample – Superstore dataset. Then you will make that dot plot better by displaying full performance ranges for a calendar year.

Hi. This is Ryan with Playfair Data TV, and in this self-guided exercise, you’re going to make an alternative dot plot that I call ranged dot plots. Ranged dot plots look not only at the period over period performance across dimension members, but also their full performance range to provide additional context.

For this example, I’m going to leave the Authoring interface on the screen so that you can get a few hints from this. But for this particular ranged dot plot, we’re going to look at the metric of Profit. So you will need to compute the current profit and a comparison profit. I recommend doing that with parameters, just to make it easy to switch. That’s what these parameter controls are there for. Then you’re going to add the context of the full performance range in the background. That’s what those gray Gantt bars are in the background. As you can see, another hint here– those Gantt marks are being sized by the Best Profit minus the Worst Profit.

So pause the video, take a shot at that, and/or look at the video How to Make Dot Plots with Performance Ranges in Tableau. When you’re ready for me to show you how this is created, hit the Play button.

All right. So I’m going to start a new worksheet. And if I glance back, we’re looking at Profit by Sub-Category. So the first thing that I would do is go ahead and create my dot plot. The dot plot is going to be created with the Current Profit calculated field. This is just saying SUM IF the month of Order Date equals the Current Month parameter. I’ll also give you a look at that really quick.

This is just an Integer data type with a range of 1 to 12. So it accounts for January through December. When you’re using months in calculated fields, like you see there in the background, months are converted to integers, which is why my parameter has a data type of Integer. So we’re currently saying, if the month equals December, then show the Profit amount. That’s what we have so far.

I’m going to click OK. And I will add the Current Profit to the Columns Shelf, which will create an x-axis. I usually like these ranged dot plots to be in a horizontal orientation. And we are breaking this down by the Sub-Category dimension. I will fit the entire view just to give us a little bit more breathing room, and change the mark type to Circle. So we have a dot plot at this point.

To get the month over month coloring, I need to use this calculation that looks at month over month profit. It’s simply taking the current month, which is currently being used on the Columns Shelf, minus the comparison month. If you see orange in a calculated field dialog, you can click on it and open this fly-out menu, and you can see the calculation. I’m just doing that instead of editing that Comparison Profit calculated field to show you that it is very similar to my Current Profit calculated field.

But instead of looking at the Current Month parameter, it’s looking at the Comparison Month parameter, which I happen to know– if I close this and look at the range dot plot from the exercise– is currently set to 11. So we’re just looking at December Profit over November Profit at the moment.

Now that I’ve got that calculated field, I’ll throw that onto Color. By default, we get this spectrum from dark orange to dark blue. Whenever I’m just looking at two outcomes– so either a positive change or a negative change, in this example– I like to double-click on the color legend and bump the steps down to 2. That gives me one color for positives, one color for negative. I’ll also go ahead and edit these and make the colors a little bit brighter. Of course, this is subjective, and you can change those to whatever you would like.

But so far, that’s the period over period dot plot. Now, to get the performance ranges in the background, I need to start by adding a second column with Gantt bars that start at the Worst Month’s Sales– or in this case, excuse me, Worst Month’s Profit.

Let’s take a look at this calculated field, because this is the trickiest part of this whole chart type. It does involve an LOD calculation, but if you focus on the LOD portion of it first, it should be pretty easy for you to think through how you’re wanting to roll up the data to get the correct answer.

What this is doing is rolling up Profit at the Month of Order Date and Sub-Category levels. As long as you’re using an element of time, you can basically repeat this LOD calculation for any scenario in your own business. So your element of time would go first, and then whatever dimensional breakdown you’re using would go second. This is going to basically create 12 rows of data for each Sub-Category. It’s going to look at SUM of Profit by Month and Sub-Category. So we’ve got 12 options to choose from, because there’s 12 months in the year, in this case.

Once we’ve got those 12 options, I’m adding an aggregation of MIN to figure out what is the lowest number in those options. So in other words, this is just the worst performer– the Worst Month’s Profit. That’s what starts the second column. So I’m going to drag that to the Columns Shelf. And by default, we’ve got another dot plot colored by month over month change. But now that the second pill is on the Columns Shelf, I can navigate to the Marks Shelf for it and edit it independently from the left side.

So the first thing is, I don’t need that coloring, so I’ll just drag that away. Also, the mark type needs to be changed from Circle to Gantt Bar. And last thing we need to do for the right side of this chart is size those Gantt marks by the performance range. Because those Gantt bars are starting at the Worst Month’s Profit, if we can compute the Best Month’s Profit minus the Worst Month’s Profit and size those Gantt bars by that range, it will push them out to the right, and the full range then will go from the worst performer all the way to the best performer.

So there were two more calculations required to figure that out. The first was Best Month’s Profit. This is just repeating or duplicating Worst Month’s Profit, but changing the MIN to a MAX. Once you’ve got both the Best Month’s Profit isolated and the Worst Month’s Profit isolated, you can make this last calculated field, which simply looks at the best month’s performance minus the worst month’s performance.

Click OK, and size those Gantt marks by that range. Make these a little bit smaller so we can see them once we add them– or convert this to a dual-axis combination chart, which is the last step. So there’s a few ways to do this, as you’ve seen on other videos. But the way most people learn is to simply click on the second pill on the Columns Shelf and click Dual Axis. That converts these into a dual-axis combination chart. It’s called that because we’ve got two axes and a combination of mark types.

This isn’t looking quite right yet, because our axes are not aligned. So a critical step is to right-click on either axis and click Synchronize Axis.

Each circle should be in that range. If you just think through the logic here, those gray bars in the background are illustrating the worst-performing month all the way to the best-performing month per sub-category, so each circle needs to be somewhere on that range. If you look at Accessories, it looks like the current month of December was actually its best performer. That’s why it’s all the way to the right. We don’t have any that are on the worst-performing month, but they all are on that range.

One last step– well, a couple of little things, but one thing I would do at a minimum is bring those circles in front of the gray bars. That’s still the main insight. The Gantt marks in the background are meant to be contextual analysis. It’s additional data you’re providing, but it’s not the primary insight.

So the way that I suggest doing that is to right-click on the axis for the marks that you want to move to the front– so in this case, Current Profit– and click Move Marks to Front. After I do that, I no longer need the top axis, so I will just right-click on it and deselect Show Header. And you’ve just created a ranged dot plot in Tableau.

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