# Can you build a dumbbell chart?

Week 32 of #WOW2022 and Kyle set this challenge to build a dumbbell chart (also sometimes referred to as a barbell chart, a gap chart, a connected dot plot, a DNA chart). I first built one of these a long time ago, and have built many since, so chose not to refer to Ryan Sleeper’s blog Kyle so generously provided. I figured I’d have a go myself from memory. Looking back now, I actually started by going down the same route a Ryan, but then switched my approach in order to handle the tooltip requirements.

So here’s what I did.

Defining the calculations required

I’m going to start with these, as it’s then easier just to explain how everything then gets built. In reality, some of these calculations came along mid-build.

I decided to create explicit fields to store the Ownership values for the 2015 & 2019 years ie

Ownership 2015

IF [Year]=2015 THEN [Ownership] END

Ownership 2019

IF [Year]=2019 THEN [Ownership] END

Both these fields I formatted to % with 0dp.

Given these, I can then work out the difference

Difference

SUM([Ownership 2019])-SUM([Ownership 2015])

also formatted to % with 0dp

and finally, I can work out whether the change exceeds 20% or not

Difference>0.2

[Difference]>0.2

These are all the fields required 🙂

Building the Dot Plot

Add Age to Columns as a discrete dimension (blue pill) and Ownership 2015 to Rows. This will create a bar chart.

Then drag Ownership 2019 onto the canvas, and release your mouse at the point it is over the Ownership 2015 axis and the 2 green columns symbol appears.

This will have the effect of adding Measure Names and Measure Values to the view.

Change the mark type to Circle and move Measure Names from Columns onto Colour. Adjust colours accordingly.

Show mark labels and adjust so they are formatted middle centre, and are bold. Increase the size of the circles so the label text is completely within the circle, and the font colour should automatically adjust to white on the darker circles and black on the lighter ones.

Add Ownership 2015, Ownership 2019 and Difference onto the Tooltip shelf, and adjust the tooltip to suit. Note – I chose to adjust the wording of the tooltip to not assume there was always an increase. I used the word ‘change’ instead and as a result applied the custom formatting of ▲0%;▼0% to the Difference field that was on the Tooltip shelf.

So my tooltip was

which generated a tooltip that looked like

Connecting the dots

Add another instance of Measure Values to Rows alongside the existing one. This will generate a 2nd Measure Values marks card.

On that marks card, remove Measure Names from Colour and add Difference>0.2 to the Colour shelf instead, and adjust the colours.

Change the mark type to Line and add Measure Names to the Path shelf.

Move the Difference pill from the Tooltip shelf to the Label shelf. Edit the label controls, so the label only displays at the start of the line, which should make the label only show at the bottom. Adjust the label alignment so it is bottom centre.

Delete all the text from the Tooltip of the ‘line’ marks card.

Now make the chart dual axis and synchronise the axis. You’ll notice the bars sit on top of the circles, and the difference text is not displaying under the circles.

To remedy this, first, right click on the right hand axis, and select move marks to back. The lines should now be behind the circles.

Then, on the lines marks card, edit the Label text and just add a single carriage return in the dialog box, so the text is shifted down.

Adding Age to be displayed at the top

The quickest way to do this is to double click in the space on the Columns shelf, next to the Age pill, and type in ‘Dummy’ or just ” if you prefer. This has the affect of adding another ‘dimension’ into the view and as a result the Age values immediately get moved to the top.

Now just remove all axis, all gridlines, all row & column dividers. Then uncheck show header against the Dummy pill and hide field labels for columns for the Age header (right click on the Age label in the view itself to get this option, not the pill). Then format the Age text to be a bit bigger and bolder, and also increase the Size of the lines mark type. And that’s your viz 🙂

If you had followed Ryan’s blog post, then you would still have needed to create separate calculated fields to store the ownership values for 2015 and 2019, but these would have had to been LOD fields eg

Ownership 2015

{FIXED [Age]:SUM(IF [Year]=2015 THEN [Ownership] END)}

and a similar one for Ownership 2019. The other 2 fields related to the difference would then need to reference these instead.

My published viz is here.

Happy vizzin’!

Donna