A couple of days ago, the Andy Kriebel (follow his blog if you aren’t already) posted Dynamic axis selections with parameters in less than five minutes. This technique enables users to pick the measure they want to view. The super-short version of the instructions are:
- Create a parameter to choose the measure.
- Create a calculated field to return the chosen measure.
- Put the calculated field on the Rows or Columns Shelf to generate the marks.
- Put the parameter on the Rows or Columns Shelf to create a header to use as the axis title.
- Delete Tableau’s default axis title.
This works great. However, when you want a dual axis chart with dynamic secondary axes, there’s a problem – there’s no way to tell Tableau that you want it to draw a header on the opposite side of the chart from the other header(s) and get the secondary axis title to accurately reflect the chosen measure.
Dashboards to the rescue!
Instead of a single view, you can use a dashboard with two (or three) worksheets. One worksheet has the left-hand (or top) headers, the left-hand – aka primary – axis with a dynamic axis title as described above, the chart, and the right-hand (or bottom) – aka secondary – axis tick marks. The second worksheet has the dynamic secondary axis title only and is formatted to size and resize the same as the first worksheet. In the dashboard below, I added a third worksheet to have a color legend that would show all the possible measure selections at once:
Here’s how to build the dashboard, assuming you’ve already got a worksheet with a view as Andy described.
- Create a “Choose 2nd Measure” parameter and “2nd Value” calculated fields to set up the 2nd measure.
- Add those to the worksheet to create a dual axis chart.
- Create “Choose Measure Color” and “Choose 2nd Measure Color” calculated fields that just return the value of each parameter.
- For each mark, put the appropriate color calculated field on the Colors Shelf.
- Select a parameter option for one of the parameters and go to the Color Legend to edit the color. Repeat for every parameter option until all colors are set the way you want. Note: It would seem like you could just use the parameter to set the color, however Tableau does not always remember your customer color settings, so that’s why there are calculated fields for the colors.
- To increase legibility, I also clicked on the Color Shelf on the 2nd mark type to increase the transparency of the 2nd measure.
- Set up any other formatting that you need on this dual axis worksheet.
- Duplicate the dual axis worksheet in order to capture all formatting/sizing.
- Drag the Choose 2nd Measure parameter onto the “Choose Measure” blue pill on Rows to replace it.
- Click on the Choose 2nd Measure pill to Format->Alignment for two things: Direction->vertical/down text and Horizontal->left. This gets us text aligned to face the chart, and the left alignment gets the text an extra pixel closer.
- Click on the Customer Segment pill to uncheck Show Header.
- Drag one of the green pills off the chart.
- On the Marks card, change the Mark type to pie and drag the Size slider at the bottom of the card all the way to the left. This creates an invisible pie chart.
- Create a calculated field called “Fake Header” with the text ” ” (just a space). Then drag that field on top of the YEAR(Date) field. This is to ensure that the top margin of the column of fake axis titles is properly set..
- Duplicate the Fake Header field and drag that onto the Columns shelf, this will create a bottom header that is to ensure the bottom margin of the column of fake axis titles is properly set.
- Unchecking Show Header for the Fake Header or hiding Column labels would end up rearranging the sizing, so we can’t use those. Instead, in the view right-click on the Fake Header header, and choose Format. In the Format Field Labels window, change the Font for the Columns to white.
- Do the same for the Fake Header (copy) that you created.
- Format->Lines->Sheet tab->Axis Ticks to None.
- Drag the right edge of the worksheet to the left to minimize the space taken up by the chart area.
- In the Dashboard Menu, choose New Dashboard.
- If you are using a fixed dashboard size, set it now.
- Drag a Horizontal Layout Container into the view.
- Drag the initial dual axis worksheet into the view.
- Drag the 2nd header worksheet into the view, just to the right of the dual axis chart.
- Turn off to the titles for both worksheets.
- Now for the futzing bit. You may need to adjust the borders of the top and bottom headers of both worksheets to get the 2nd header fake axis titles to be in the same position within each pane. to fit, especially when you are using a fixed dashboard size.
- Turn off the borders for the 2nd header fake axis title by going to the worksheet and choosing Format->Borders->Row Divider->Pane: None and …Column Divider->Pane: None.
- Back in the dashboard, click on the 2nd header worksheet and resize the width so it takes up less space.
For setting up the Color legend, I used a separate worksheet using Square marks with Measure Names on the Rows, Color, and Label Shelves, and a fake axis so I could control the alignment. See the Tableau Public workbook above for more details.
And there you have it. Thanks to Andy for the inspiration, and thanks to the Tableau Community forums for the initial question – the workbook I used here is based on one I’d created awhile back for this Tableau forum post: Dynamic header names.