How to Make Dot Plots with Performance Ranges in Tableau

Add value to traditional dot plots with ‘ranged dot plots’

Ranged dot plots show not only the current performance and period over period performance change across dimension members, but also where each dimension member has been throughout a calendar year!

Hi. This is Ryan with Playfair Data TV, and in this video, I’m going to show you how to make an alternative dot plot I call ranged dot plots. Ranged dot plots show not only the period over period performance across dimension members, but also the full performance range of each dimension member. So in other words, you’ll see the period over period performance as the dot plot, but in the background, you’ll see where that dimension member moved from its worst performance all the way up to its best performance. It adds even more context to one of my favorite chart types.

To start over here in Tableau Desktop, the first thing we need to do is create the period over period dot plot. You can use this with any measures and any periods that you would like. But for the purposes of illustration, I’m going to setup two parameters that allow my end user to choose a current month and a comparison month. So the parameter will have a data type of Integer, because when we translate this later in a calculated field, months are converted to integers. And I’ll go ahead and provide a range from 1 to 12. So if you’re not familiar with this, this is just looking at whole numbers between 1 and 12– so in other words, the months that are available to us to choose in a year.

Now that I’ve got the first one, the second one is very easy to create, because I can just duplicate the first one. And I will call this my Comparison Month. Same settings as the first. I will show both of these, just so that we have one click access to changing them. And let’s go ahead and set up a scenario. I’ll just pick a couple of random months. And we will pretend that we’re looking at August versus July, which is what those settings will currently do.

Next thing we need to do is set up a calculated field that computes that month over month difference. So I’m going to call this Period over Period Change. And actually, I’m going to do this– there’s two ways to go about this. We could nest these together, but a lot of times, I want to use the individual month’s performance in multiple places. So I’m actually going to go about this a little bit differently. I’m going to first compute the current month’s performance for a specific measure. Then we’ll come back and compute the comparison month, and then we will do a period over period change.

So before I get to this one, I’ll call this my Current Month’s– and then I have to pick a metric. I’m just going to look at Sales for this illustration. And the formula would be SUM IF the Month of Order Date equals the value in that Current Month parameter, then I want Tableau to show the Sales. And close parentheses. So there is the formula for Current Month’s Sales. I’ll click OK.

I will duplicate that one, because again, I just have a couple of small tweaks to make here. First, I want to change the name from Current Month’s Sales to Comparison Month’s Sales. And instead of looking at the value of the Current Month parameter, I want to change that to the Comparison Month parameter. So there’s just a little bit of a setup for this chart type. We’re just isolating the performance for the current month and a comparison month. This is valuable outside of this video as well. This will be a handy calculated field for you.

I’m going to click OK. And now I’m ready to set up the dot plot. The dot plot will be created with Current Month’s Sales. So I’ll just double-click on that to get it onto the view. And let’s say that we’re looking at this by the Region dimension, so I will drag Region onto the view. And with dot plots, for whatever reason, I tend to prefer these to be in a horizontal orientation instead of a vertical orientation, which the default is doing here. So I will just click this Swap button to swap my Rows and Columns shelves. I’ll fit the entire view so that we can see this a little bit better. And I will change the mark type to Circle, and make those circles a little bit larger.

So at this point, we have a dot plot showing Current Month’s Sales– in other words, Sales for the month of August– per Region. At this point, I would like to layer on a little bit of additional context in the form of a period over period comparison. That’s the third calculated field. There’s two ways to go about this. I could create a calculated field and do Current Month’s Sales minus Comparison Month’s Sales.

I’m going to show you a little shortcut to use Tableau in the flow. If I just double-click on the Marks Shelf and type Current Month’s Sales minus Comparison Month’s Sales, that will actually create a calculated field for me, and it will be added to the Detail Marks card. That’s what that icon is telling me.

But now that it is on the Marks Shelf, I can simply move it to the Color Marks Card. And these dots are now colored by their period over period performance. Maybe make these a little bit brighter. Oranges and blues. So orange for low– or negative, in other words– and brighter blue for positive. Maybe bump the steps down to 2. Click OK. And now my positive period over period changes are colored blue. My negative period over period changes are colored orange.

This dot plot’s already getting better. I already like a dot plot. It’s very minimalist. I’m able to use the pre-attentive attribute to see the spatial distribution of these dots on the x-axis. We’ve made it even better by adding in the pre-attentive attribute of color. These are now also colored by their period a period change. So we’re getting even better.

This is already a pretty good chart. But we’re going to make it even better than it is now by adding a range in the background, and I call these ranged dot plots. The first formula that we need is the Worst Month’s Sales for each dimension member. This is the trickiest part of this whole chart, but I’m going to hopefully talk you through the logic and how I go about thinking about how to write this to make it easier for you to decide.

It is a level of detail calculated field. I’m going to call this Worst Month’s Sales. And this is a fixed LOD, so it starts with an open curly bracket and the word FIXED. Make this a little bit bigger so we can see it. And here’s the part where you just have to think through, what are you trying to calculate? We’re trying to calculate the Sales, but roll it up at the Region and Monthly level, because once we know all the different Sales amounts per Month and per Region, we can then add an aggregation of Minimum to figure out what was the lowest or worst-performing month per region.

You can write this in different orders, but what I suggest you do is start with the level of detail portion of it, because once you get it rolled up to the appropriate level, then it’s only a matter of wrapping that LOD calculation in another aggregation. Again, if you’re not familiar with level of detail expressions, which I encourage you to check out in another video, it’s one of the only times in Tableau where you can have an aggregate of an aggregate.

So we’re going to do a fixed level of detail calculation with an aggregation of SUM. That sum is going to sum up Sales at the Region and Month of Order Date level. Once it’s rolled up and aggregated to that level, we’re going to take an aggregate of an aggregate and take the Minimum of that sum. That’s going to get us worst month’s sales per region.

So what we’re looking for is to roll this up at the Month of Order Date. And we can address multiple dimensions within a level of detail expression. So there is the first one, but we also want whatever dimension we’re breaking this chart down by, which in this case is Region. So those are my two dimensions. What comes next is a colon punctuation mark. There’s that first aggregation SUM, and we’re looking at the measure of Sales. Close parentheses, close curly bracket.

So there’s the first piece of it. Now that we’ve got this rolled up to the correct level, I’m going to go to the very beginning of it and type the aggregation MIN. This is going to take the worst month’s sales per region. And I’m going to click OK. And I’m going to add this as a second column. So I’m just going to drag that to the Columns Shelf. And that creates a second Marks Shelf, and these Marks Cards can be edited independently of the first.

So the first change I’m going to make is, I’m no longer going to color those circles by the month over month change. I’m also going to change the mark type to Gantt bar. And I’ll make this a little bit smaller– we’ll make it even smaller a little bit later on– but just so we can see the Gantt bars. Those Gantt marks currently should be at the Worst Month’s Sales per Region.

What I suggest you might want to do, if you’re not comfortable with these LOD calculations, is make a quick table on another sheet and just do some quick quality checking. So for Central, if I’m looking across here, looks like the lowest performance was $8,211. If I look at the Gantt mark, it says Worst Month’s Sales, 8,211. So I’ll trust that the rest these are correct for time here and keep moving.

The next thing we need to compute is the best month’s sales, because our full performance range in the background is going to go from the worst month to the best month. Since we already went through the trouble of thinking through the logic of how to compute the Worst Month’s Sales, what I suggest you do is simply duplicate the Worst Month’s Sales calculated field and call this Best Month’s Sales, and change the MIN to a MAX. That’s Best Month’s Sales.

Now, to get the range to work, we need to size the Gantt marks in the right column by Best Month’s Sales minus Worst Month’s Sales. Because those Gantt marks start at the Worst Month’s Sales, if we size them by best minus worst, it’s going to push them to the right. That’s what’s going to create this illusion of a performance range in the background.

Just like with our month over month calculation, there’s two ways to do this. We could set up a calculated field, and we could say Best Month’s Sales minus Worst Month’s Sales. That’s the whole formula. Or you could add this in the flow. So if I just cut that out, go to double-click on the Marks Shelf for the Gantt bars and paste that in, click Enter, that by default will add that calculation to the Detail Marks Card. This time, I’m going to drag that to Size, and we’ll start to see these bars come together.

Last couple of steps are to convert this into a dual axis combination chart. I’ll do that by clicking on the second pill on the Columns Shelf and clicking Dual Axis. These should be in sync. So I’m going to right-click on either axis and click Synchronize Axis. And I want to make my Gantt bars significantly shorter so that I can see the circles on top of them. So I’m going to click on the Size Marks Card and drag this down to the left quite a bit. This is a case-by-case basis. It’s subjective. But get it to the look and feel that you’re looking for.

One last thing. You can see that those circles are now behind the Gantt bars. There’s a couple of ways to change this. You might know that you can just drag one pill in front of the other. That will reorder the axes, and therefore reorder where the marks are located. However, the drawback with that is your primary axis– well, I would consider this the primary axis– is now showing Worst Month’s Sales instead of Current Month’s Sales.

So I’m going to undo that and show you one more way. You can right-click on the axis, and depending on which one you click on, this will either give you the option to move the marks to the back or move the marks to the front. In this case, we’re trying to move those circles to the front. So I’m going to click Move Marks to Front. And this is starting to look a lot better now. And I can now hide that top axis by right-clicking and deselecting Show Header. And we have a dot plot showing not only a period over period change for those circles, but also their full range of where they went at their worst time during the year all the way to their best time in the year.

So how I would read this at a glance is– first thing I’m noticing is we’ve got two regions that had a period over period decline and two regions that had a period over period increase. I can also see that none of them in August were anywhere close to their best performance, and it looks like South was the closest to its worst performance for the year.

Those ranges in the background are going to remain static until we refresh our data and we have a better or a worse performer. Those circles are going to change, though. So let’s say I wanted to change my comparison. Let’s say it’s now September. If I change this to September versus July, one thing that stood out to me is, Central jumped all the way to its best performance. So it looks like September was the best time for the Central region. Obviously, because it was the best, it was also up period over period, which is why it’s colored blue.

But notice as I shift the current month here, those circles are going to move on the dot plot, but those performance ranges in the background are always going to remain static to give us that context of where we’ve been throughout the year.

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