Can I axis you a question? How do you make an axis dynamic? We’re moving away from the Once More Unto the HIPAA Breach dashboard (HIPAA breach reviz) now, to other topics pertaining to other dashboards. The dynamic axis in particular is for this particular dashboard, It’s a Punderful Life: A Punvironmental Impact Study on DSNY, and specifically this butterfly chart:
The butterfly chart was made with two worksheets, one for each bar chart, but see how they’re both on the same axis (i.e., the same scale), whether the total number of puns is selected or the number of puns per week?
If this was a static butterfly chart, we could have just fixed the axes for both to end on the same value. However, because it’s a measure switch, it’s not so simple, so today, I’ll go over how to create a dynamic axis.
This tip can also be used to dynamically add a little space between the end of the chart area and the entire chart itself (more useful for line charts, as the axis is based on time):
Note: Another way to create a butterfly chart is to create both bar charts on the same worksheet, but I used two worksheets due to wanting labels for each bar between the two bar charts.
The Process
#1: Set Up (Half of) the Static Visualization
1. Let’s start with our basic butterfly chart, showing the number of puns by weekday. Drag # of Puns to Columns and Day of the Week to Rows.
2. Filter to one of the two pun-making rival sides, which is the column Pundit (values in this column are Homepun = myself and The Punvironment = everyone else). Drag Pundit to Color on the Marks Card. I’m going to create the right side of the chart first (the pink side), so I can leave the axis as is.
3. As I’m going to create separate labels for each bar (shared with the corresponding bar for the other bar chart), I’m going to hide the Day of the Week headers. I’ll also hide the # of Puns axis and turn on the bar labels instead.
Note: I want to be able to duplicate this chart for the other half of the butterfly chart with minimal editing for the duplicate, so I’m going to hold off on creating the other half for now, though I will duplicate it briefly and then delete it once I’ve checked my work.
Here’s what our chart looks like so far:
#2: Bring in the Measure Switch Parameter
The chart currently shows number of puns; I want to be able to switch between showing number of puns and also the number of puns per week.
1. Let’s start with calculating the number of puns per week. Create a calculated field # of Puns / Week:
SUM([# of Puns]) / COUNTD(WEEK([Date]))
While # of Puns is its own field, and each row shows the number of puns by day and pun-maker (Pundit), we need to “aggregate” # of Puns with SUM() because the denominator (the number of unique weeks in the dataset) is an aggregate; otherwise Tableau will return an error.
The result we get has a lot of precision/decimal points, so I’m also going to wrap the entire result in ROUND() to control the number of decimal points in the results. In this case, I just want one decimal point.
The final result of the calculated field now looks like this:
ROUND(SUM([# of Puns]) / COUNTD(WEEK([Date])), 1)
2. Replace SUM(# of Puns) on Columns with AGG(# of Puns / Week) briefly to check our work:
3. Create the measure switch parameter. I called it # of Puns or # of Puns/Week Parameter. The two string values for the parameter are TOTAL # OF PUNS and # OF PUNS/WEEK. Right-click on the parameter and select Show Parameter.
4. As with all newly created parameters, it won’t affect the visualization just yet; we’ll need to create a calculated field first. It’s an unoriginal name, but I just called it # OF PUNS OR # OF PUNS/WEEK:
IF [# of Puns or # of Puns/Week Parameter] = "TOTAL # OF PUNS"
THEN SUM([# of Puns])
ELSE [# of Puns / Week]
END
This calculated field says, when TOTAL # OF PUNS is selected, return the # of puns. Otherwise, show the # of puns/week (we don’t need to write out the other value in the parameter because there are only two options in this case).
5. Now, let’s test!
6. Notice how when TOTAL # OF PUNS is selected, there’s a decimal place for the bar labels, even though it’s not necessary (# of puns is an integer)? I’m going to create another calculated field called LABEL: Number Formatting for Butterfly Chart to fix that:
IF [# of Puns or # of Puns/Week Parameter] = "TOTAL # OF PUNS"
THEN INT(SUM([# of Puns]))
ELSE [# OF PUNS OR # OF PUNS/WEEK]
END
When # OF TOTAL PUNS is selected, INT() wrapped around SUM([# of Puns]) forces the # of puns to display as an integer (i.e., no “.0” at the end of the value).
7. Drag LABEL: Number Formatting for Butterfly Chart to Text on the Marks Card, and test again.
#3: Make the Axis Dynamic
Alright, now we’ve created half of our butterfly chart with the measure switch. To make sure the bars for both halves can be compared accurately, we need the max value of the axis for each chart to be the same.
For example, for # of puns, we could fix the axes for both butterfly charts at 50. However, for puns/week, that max value wouldn’t make sense, since the range is much smaller–hence why we can’t just simply fix the axis at a static value.
What can we do instead?
Well, we need the max value for the axis to change with the parameter, so we’ll definitely need some sort of calculated field.
1. Let’s start with creating that calculated field, called # OF PUNS OR # OF PUNS/WEEK - AXIS. I want the max value to be 50 when # of puns is selected and 3 when puns/week is selected.
IF [# of Puns or # of Puns/Week Parameter] = "TOTAL # OF PUNS"
THEN 50
ELSE 3
END
2. Now that we have the calculated field, where are we going to put it?
We’re going to use this field’s values in an invisible reference line! Because the max values for # of puns and # of puns/week are set in this calculated field as always being slightly greater than the highest value in the chart, the bars won’t ever be in danger of getting cut off, and the reference line’s value will change depending on what’s selected in the parameter.
Drag the calculated field to Detail on the Marks Card. Then, in the Analytics Pane, drag a Reference Line to the canvas.
3. For Value, select the calculated field, and the worksheet will update, even before you hit OK. But wait, what happened here?
The bars are very short, and the value for the reference line is 4250, not 50 as we wanted for the number of puns. What happened here?
It has to do with how we’re computing the value. When we dragged the calculated field to Detail, we left it in the default aggregation of SUM. There’s also 85 values in the dataset, one for every weekday (5) for 17 weeks. 5 * 17 * 85 = 4,250.
4. If we exit out of the window and change the aggregation to AVG, we get a little closer–now the reference line’s value is 250. The reference line is still summing the 50s together; 5 bars * 50 = 250.
Right-click on the reference line again and change the aggregation in the window to Average. Now, when we toggle the parameter, we see the desired result of the reference line being 50 for # of puns and 3 for # of puns/week.
5. We don’t want this reference line to be visible, as it’s not analytically important, so remove all labels, tooltips, and colors/fills.
#4: Create the Other Half of the Butterfly Chart
1. Final step to close this out is to now finally duplicate this sheet for the other half of the butterfly chart.
2. Edit the filter to now choose the other value (Homepun). Pundit is still on Color on the Marks Card, so I’m going to now set the color to blue. I’m also going to briefly show the axis, so that I can right-click on the axis > Edit Axis > Reversed (for Scale).
3. It wouldn’t be a butterfly chart if we just left the worksheets like this, so let’s put the sheets together on a dashboard, with text boxes for the row labels.
4. Finally, create buttons for the parameter actions, so that the user can click on one of the buttons to trigger the measure switch.
And see? You can’t tell that the reference lines are there at all! Hope this was helpful :)