Comparable Analysis with Filter-Specific Dual-Axis Line Charts in Tableau

I was recently tasked with creating a visual that would take any metric (measure) and have two lines associated with it.  The first line would be the overall trend across the selected date range.  Easy enough.  Date field on Columns and measure on Rows.  The second line, however, needed to be a dynamic line that would allow the users to select a single variable or create a cohort of said dimension.  For comparison purposes, both lines had to be on the same chart.

I knew that this called for a dual-axis line chart but had to think about how I could separate these two lines from certain filters. I thought maybe a level of detail (LOD) calculation would do the trick.  Then I remembered that having filters for line 1 would still affect the overall view.  I figured I could create a set, but the way I deployed that method didn’t quite work.  There was something missing or perhaps I needed to use an alternative view.

Enter the Flerlage Twins.

Kevin and Ken have made tremendous contributions to the Data Fam; there’s just so many to list, but among them is their ability to think outside of “Show Me”, proving anything is possible in Tableau.  They’ve both taken the time and guided me on multiple opportunities; provided me with so many pointers that have allowed me to grow with this visualization tool.  I truly believe I speak for all of the Tableau Community when I say that we are all so grateful for them.

So, it should be no surprise that Kevin said the multiple lines with different filters is a go in Tableau.  As I mentioned earlier, I had thought of sets but did not set it up in a way that would achieve what we needed. Kevin suggested set controls.  A set, the set as our filter, and a calculated field using our set to grab the aggregated measure placed on the Rows shelf. 

Here it is explained in a few steps using Sample-Superstore:

Create the Set

Right-click on the dimension you want to apply to the second line. 

Create Calculation

Now that we have the set, let’s create a calculation that grabs only the measure value for manufacturers in that set.  In this example we are using Sales.  Name it “Sales using Manufacturer” and type the following formula:

// Sales for the values in the Manufacturer Set

IF [Manufacturer Set] THEN [Sales]

ELSE NULL

END

As a note, if you are dealing with aggregated calculations or measures, you will need to wrap that in a FIXED statement to avoid aggregate and non-aggregate errors.

Build the Viz

Drag a date field to your Columns shelf.

Drag the Sales field to the Rows shelf.

Drag the Sales using Manufacturer field to the Rows shelf and create a dual-axis chat. 

Should have something that looks like this now:

Test the Set

Go to the Data Pane and right-click on the Manufacturer Set field and select Show Set

We should still see one line since by default all manufacturers are selected.  This just means that both lines hold the same values. Click All in the Set to uncheck everything and then select a few manufacturers.  You should now see two lines since we have an overall value and dynamic cohort value.

Now you can add filters that will affect the entire view, but our lines allow for different levels of granularity.

And that’s all there is to it!  Thanks Kevin.

Link to the twbx on Tableau Public here.