Two Ways to Make Dumbbell Charts in Tableau

An Engaging Option for Comparing Two Dimension Members

As opposed to a dot plot alone, dumbbell charts help communicate what direction your end users should read comparisons. You’ll see how to create dumbbell, or DNA, charts regardless of whether your data is in a tall or wide structure.

Hi, this is Ryan with Playfair Data TV, and in this video, I’m going to show you two different ways to make dumbbell or DNA charts. The two different ways are dependent on how your data is set up, so what’s going to be helpful about this video is I’m going to show you how to make this very engaging chart type, regardless of how your data is setup.

First, my preferred way– and this is how you would build a dumbbell chart if your data was set up in what’s called a “tidy” format, where you’ve got one column per field name. To illustrate over here in Desktop, we’re on the Sample Superstore dataset. And let’s say that we want to compare sales performance between 2018 and 2017 across our 17 sub-categories. Well, I always start with my measure, so let’s start with Sales. I’m just going to double-click to get that onto the view. And the default behavior when I double-click Sales was for Tableau to put that measure onto the Rows Shelf. This creates a y-axis, so a vertical orientation for my bar.

Most people, when they’re creating dumbbell charts or DNA charts, prefer a horizontal orientation. That’s where the name comes from. This will eventually look like little dumbbells or strands of DNA. That’s where the name comes from. So I’m going to move that Sales measure from the Rows Shelf up to the Columns Shelf. And as I mentioned, we’re going to break this down by the dimension called Sub-Category, so I’ll find that and place that onto the Rows Shelf. I will also fit the entire view, just so we can see this a little bit better.

When I’m making this chart type, I prefer to make it when I’m comparing just two data points. I’ll show you later on that this does work with more than two data points, but that’s what we’re going to start with. We’re going to compare the year 2018 to the year 2017 in the sample data. So the first thing I’m going to do is just add a discrete date filter on the Year date part to keep just the last two years in the data set.

I’m going to click OK. You saw a little bit of shift in the data. What I want to do now is color these marks by the year of Order Date. So I will place Order Date onto the Color Marks Card. And because the default date part is Year, what we’re left with are one color for the year 2017, a second color for the year 2018. At this point, we have a stacked bar chart.

This is one of my least favorite chart types, because if you’re not the stack on the bottom– so in this case, 2018– it’s very hard to analyze the trend of the stack on top, because 2017, at the moment, is inheriting the values from 2018 below it. It’s pushing it out to the right. So at a minimum, I always try to convert my stacked bar charts to dot plots, which you can do by simply changing the mark type right here from Bar to Circle.

So a little bit better– I’ll make these– I’ll click on the Size Marks Card and drag this slider over to the right, just so we can see the circles a little bit better. To convert this dot plot into a dumbbell or DNA chart, the first thing we need to do is duplicate the Sales measure on the Columns Shelf. This is an application of one of my favorite shortcuts in Tableau, which is to hold down the Control key while I click on a measure, and that creates a copy of that pill. I said measure, but that could be any pill, a dimension or a measure. If you hold down the Control key while you left click on it, it will create a copy of it.

What is important about this is now that there are two different measures on the Columns Shelf, they each get their own set of Marks Cards over here on the left. And those marks can be edited independently of each other. So in this case, I’ll leave my left side as-is, but I will navigate to the Marks Cards for the second column, and I can do a couple changes here. First, I’m going to change the mark type from Circle to Line.

So currently, on the left, we’ve got a dot plot, Sales by Sub-Category and Year. On the right, we have a traditional line graph that’s connecting the dots by Sub-Category. It doesn’t make a whole lot of sense to do that. The easiest way that I have found to convert these lines, this line graph into lines that will connect the dots in the correct direction, is to simply move the dimension that is currently encoding the marks from the Color Marks Card to the Path Marks Card. This will tell Tableau how to kind of change the direction on how we’re connecting these dots.

So if I click on that dimension and move it to the Path Marks Card, you’ll start to see this come together. Now we’ve got some nice crisp lines that are connecting the year 2017 value to the year 2018 value. Lastly, I need to convert what we’ve got so far into a dual-axis combination (chart). There’s a couple ways to do this. Most people learn this by clicking on the second measure and clicking Dual Axis.

I will undo and show you how to save exactly one click. If you hover near the second axis, a green triangle will appear. You can left-click on that green triangle and drag it somewhere. So I can left-click and drag that straight to the other axis, and that dashed line is showing me where it is going to draw an axis. We end up at the exact same spot. I just saved myself one click– a little bit of a shortcut.

Couple of last things just to clean this up– whenever you have a dual-axis combination chart where both axes should be on the exact same scale, you’ll want to ensure those are synchronized, which you can do by right-clicking on either axis and clicking Synchronize Axis. Watch when I click this. You’ll see just a tiny shift, but now I’m guaranteed that my circles line up with those gray lines on top of them.

Speaking of the gray lines on top of the circles, I would prefer that the gray line be underneath the circles. I think that would look a little bit nicer. There’s a couple of ways to reverse the order of these layers. One is you can right-click on either axis, and depending on which one you click on, this will either say, move marks to back or move marks to front. If I click on that, you’ll see that the gray line goes below the blue and orange circles in this case.

I’m going to undo just to show you one more way. You can simply drag one of these pills on the Columns Shelf in front of the other. So if I left-click on the second Sales pill, and drag it in front of the other. Same thing– I just reordered those layers, and now this looks nice and polished. And I guess one last thing to really finalize it– we no longer need both of these axes. They’re on the same scale, so that’s redundant data. I will hide the one on top by simply right-clicking and deselecting Show Header.

I mentioned that I prefer to use this type of chart when I’m comparing two dimension members. I actually think that this is slightly better than a just traditional dot plot, because it is implying the order in which my users should be reading this. Without those lines in the background, this just kind of looks like a scatter plot. When I add the dumbbell or DNA look, it actually implies a relationship between those two dots, so I actually prefer this chart type, but I think it works best with two dimension members.

Just to show you, if I were to remove my Year filter, I can compare more than two dimension members. This is still a nice look. I just don’t think it’s as clear as when you’re comparing just two dimension members, which is why I recommend that.

All right, the second way that your data might be laid out would be is if you had a measure for each year’s sales individually. So in the Sample Superstore data, it’s kind of perfectly set up for us. We’ve got a year. We’ve got a column called Year, so we can encode those sales values by the year that they’re in. That was the first method that I showed you. In real life, you might have a wider data set, where all of your different measures are broken out into their own columns. So you might have a column called Sub-Category, then a column called 2017 Sales, and a third column called 2018 Sales.

I’m going to show you how to make this same chart type if your data is in that latter format. So you’ve got individual columns for all of your fields. I’ve done a little bit of extra work to prepare to show you this. I’ve just isolated the year 2017 sales and the year 2018 sales. Let me show you what that looks like under the hood here. So I’m basically just kind of unpivoting this data source so that it might look like a wider data source instead of a taller data source.

If your data is in this format, you will need to use these special generated fields called Measure Names and Measure Values. I’m going to show you this on a new sheet. First thing I’m going to do is place the Measure Values field onto the Columns Shelf, and I will break that down by the same Sub-Category dimension, and once again, fit the entire view.

The important thing during this step is to exclude all of the measures that you don’t need. When I added Measure Values to the Columns Shelf, it brought in every single measure in the data source. Well, we only need these first two, so I can drag the rest of these away, and it will just filter them out. If you prefer not to drag those off individually, you can click into this Measure Names filter on the Filters Shelf, and just deselect them here. It does the same thing.

But we’re trying to end up with just the two measures that we are comparing. I’m going to go ahead and click OK. And now, we still want to color these marks, but this time, instead of coloring them by Year, we’re going to color them by this other generated field called Measure Names. I’m going to drag the Measure Names dimension to the Color Marks Card. Once again, we have a stacked bar now. You can kind of start to see this coming together. I will change the stacked bars to circles by changing this mark type from Bar to Circle. Make these a little bit bigger.

Just like I duplicated the Sales measure in the first example, I will duplicate the Measure Values pill in the second example. Hold Control, left-click on that pill, and drag it right next to itself. From here, I’ve got two measures. Those get their own set of Marks Cards. Those marks can be edited independently of each other, so I’ll navigate to the second version, change the mark type to Line, move the Measure Names pill from the Color Marks Card to the Path Marks Card, and lastly, duplicate or turn this into a dual-axis combination chart.

And as you can see, once again, these look like little small dumbbell charts or DNA strands, which is where the name comes from. I just showed you how to create this kind of special chart type that is not in Show Me, regardless of how your data is setup.

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