Can you build a butterfly chart?

Lorna Brown set the challenge this week to build this butterfly chart, so called because of the symmetrical display. I’ve built these before so hoped it wouldn’t be too taxing.

The data set isn’t very verbose, fields for gender & year specific values by age bracket

When connecting to this excel file, you need to tick the Use Data Interpreter checkbox which removes all the superfluous rows you can see in the excel file.

Now, I started by building a version that required no further data reshaping, which I’ve published here. This version uses two sets of dual axis, and I use the reversed axis feature to display the male figures on the left. This version required me to float the Population axis label onto the dashboard so it was central. Overall it works, the only annoying snag is that I have two 0M labels displayed on the bottom axis, since this isn’t a single axis. As a result, I came up with an alternative, which matches the solution, and which I’m going to blog about.

Reshaping the data

So once I connected to the data and applied the Use Data Interpreter option, I then selected the Males, 2021 and Females, 2021 columns, right clicked and selected Pivot

This results in a row for the Male figures and a row for the Female figures, with a column Pivot Names indicating the labels for each row. I renamed this to Gender, and the Pivot Values column to Population.

Building the Chart

On a sheet, add Age to Rows and Population to Columns and Gender to Colour. Set Stack Marks Off (Analysis > Stack Marks > Off).

You’ll see that both the values for the Male & Female data is displaying in the positive x-axis. We don’t want this. So let’s create

Population – Split

IF [Gender]= ‘Males, 2021’ THEN [Population]*-1 ELSE [Population] END

Replace the Population pill on Columns with Population Split

So this is the basic butterfly, but now we need to show the Total Population. Firstly, let’s create

Total Population per Age Bracket

{FIXED Age: SUM([Population])}

And then similarly to before, we need to display this in both directions against the Male and the Female side, so create

Total Population Split

IF [Gender]= ‘Males, 2021’ THEN [Total Population per Age Bracket]*-1 ELSE [Total Population per Age Bracket] END

Drag this field onto the Population Split axis until the two green column icon appears, then let go of the mouse. This is creating a combined axis, where multiple measures are on the same axis.

Move Measure Names from Rows to the Detail shelf and then change the symbol to the left, so the pill is also added to the Colour shelf. Adjust the colours of the 4 options accordingly.

Edit the axis and rename to Population. Then Format the axis, and set the scale to display in millions to 0dp

The tooltip needs to display % of total, so we’ll need

% of Total

SUM([Population]) / SUM([Total Population per Age Bracket])

Add this to the Tooltip shelf

We also need the population figures on the tooltip displayed differently, so explicitly add Population Split and Total Population Split to the Tooltip shelf. Use the context menu of the pills on this shelf, to set the display format in the pane to be millions to 2 decimal places. But this time, once set using the Number(Custom) dialog, switch to the Custom option and remove the – (minus sign) from the format string. This will make the values displayed against the Males which are plotted on the -ve axis, to show as a positive number.

Finally, set aliases against the Gender field, so it displays as Males or Females (right click on Gender > Aliases).

Now you have all the information to set the tooltip text.

Now add a title to the sheet to match that displayed, hide field labels for rows, and adjust any other formatting with row/column dividers etc that may be required, and add to a dashboard.

And that should be it. My published version of this build is here.

Happy vizzin’!



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s