At the Boston Tableau User Group meeting this past month, Anthony Chamberas and I got to talking and he posed this brain teaser: he wanted a dashboard to show a bar chart showing volume for each region, and be able to pick a region and then on a separate line chart see the performance of that selected region over time compared to the remaining amounts. I kept on thinking about it on the ride back to Maine, and between the Kennebunk exit on I-95 and home I put together this proof of concept while Catherine Rush drove and kindly listened to me think out loud.
While we can set up views where we show one vs. the rest via parameters or sets, for this dashboard we want a user to be able to click on one or more bars and see something different in the lines. In Tableau, that calls for a Filter Action. The key bits to know are that Filter Actions:
- Work on dimensions only (not measures or table calcs)
- Require the dimension(s) being filtered to exist on both the origin and target worksheets
- Either include (or exclude) the data from the target worksheet(s) – the default is to include, once a Filter Action is created, we can edit the filter to exclude data
- Are processed at the same time in the pipeline as other dimension filters – in other words, prior to table calculation filters
Given that, we know that the Filter Action will be removing data needed to compute the total sales minus the selected sales. To bring an unfiltered total into the view, we can use a duplicated data source that doesn’t blend on that filtered dimension – Region in this case. That’s a mouthful, here’s an example worksheet showing how the filter will change results for the regular measure from the primary, but not the secondary:
A self-blend to get an unfiltered total is pretty useful, if you are using extracts you can even self-blend to the extract (or, better yet, a Tableau Saved Data Source) so as to not double your storage requirements.
Here’s the bar chart, using Superstore Sales:
And here are the beginnings of the line chart. The blend is on Order Date and not Region:
The only other change is that I took Region off the Color Shelf for the Sales from Secondary measure, set the color to grey, and made it a little thicker:
Now we can put the two in a dashboard and click on the Bar worksheet’s “Use as Filter” context menu to use it to filter the other worksheet. Alternatively, you can build the Filter Action like so:
And here’s the starting dashboard, try clicking around:
There are a few things to note on this dashboard:
- When no bars are selected, the grey line is thicker and a little fuzzy, at least on Tableau Desktop, not so visible on Tableau Public. This is because of overprinting – Tableau is drawing a line for every Region.
- Also when no bars are selected, the view draws 4 colored lines, when we want just the single total line.
- Clicking on one or more bars filter for the particular Region, but doesn’t change the total of the sales from secondary. This is expected because we haven’t set up any calculation yet.
This leads to a couple of tasks that need to be done:
- Set up the selected sales measure for the colored line(s) from the primary to not show when no bars are selected.
- Set up the grey line to only draw one line for the total sales minus the total of all the selected sales
Selected Sales Measure: the colored line(s)
Here’s the formula for the Selected Sales measure, which will have a Compute Using of the Region (so it is partitioned on the Order Date):
IF SIZE() < COUNTD([Unlinked Region Sample - Superstore - English (Extract)].[Region]) THEN SUM([Sales]) END
This gets a little tricky because the Selected Sales measure is set up to evaluate what’s in the view given the Filter Action selection and return different results, I’ll walk through each part:
- The SUM([Sales]) measure returns the sum of Sales for each of the selected Regions. That gets us multiple lines.
- COUNTD(Region from the secondary) always returns the total number of Regions in the secondary for the current Order Date, no matter the Filter Action selection, because Region is not part of the self-blend.
- SIZE() is being used as a count distinct in this case, it returns the number of Regions in the view.
The evaluation says that if the # of Regions that are selected is less than the total number of Regions, then return the sum of Sales for each Region, otherwise return Null. The Null is what gets rid of the marks when no bars (Regions) are selected or all Regions are selected.
Sales – Total Selected Sales: the grey line
For this measure, we need to have our total unfiltered sales (the sales from secondary) and subtract from that the total of the selected sales, here’s the formula, which also has a Compute Using of the Region:
IF FIRST()==0 THEN SUM([Unlinked Region Sample - Superstore - English (Extract)].[Sales]) -ZN(WINDOW_SUM([Selected Sales])) END
Again, we’re setting up a measure to be responsive to the Filter Action, here’s what’s happening inside it:
- ZN(WINDOW_SUM([Selected Sales])) sums up all the Selected Sales across all Regions, and if there are none selected then returns 0.
- SUM(Sales from the secondary) is the unfiltered total sales.
- IF FIRST()==0 is used to only return a single non-Null value no matter how many Regions are selected, this gets rid of the overprinting.
With these two measures, we can set up the dashboard:
There are two other things that I did with the dashboard to make it work well:
- Turned off the Nulls warning on the line chart.
- Created a measure called Sales Spacer (Line) that is SUM(Sales from the secondary) * 1.1. This is an invisible reference line (set to the Max value) that ensures that the axis will have a fixed range and won’t change as the data is filtered. We could manually fix the axis range, but then if we added new data we’d have to remember to re-set the axis. The invisible reference line trick makes the axis range responsive to the range of the data.
This view is a fun example of how we can combine Filter Actions, data blending, and a little dose of table calculations to create a rich interaction. Anthony emailed me back to say he’s putting this into use right away to help his customers get a clearer view of their campaigns as they test one against others.
Adding an Area Chart
When writing up the above and doing a lot of clicking around, I wasn’t quite so happy with how much the lines jump around as we do the filtering, so I created an alternative with an area chart. Here’s how to build the chart – it’s about 20 steps that go pretty quickly:
- Duplicate the line chart.
- Drag the Action (Region) pill off the Filters Shelf. This confuses Tableau while we’re building the view.
- Drag the Sales – Total Selected Sales measure to the measure values drop area:The view will now have Measure Names/Measure Values and the pills will be red because Tableau decided to remove Region from the Level of Detail Shelf. Why Tableau does this, I don’t know.
- Drag Region from the Dimensions area to the Level of Detail Shelf. The view will now show two lines.
- Change the Mark Type to Area. You’ll see an Area chart at the top and nothing at the bottom (because we haven’t filtered out any Regions, the Selected Sales measure returns only Null values).
- Drag Measure Names from the Dimensions area to the Color Shelf. Now you’ll see only one Area chart.
- The next few steps are for getting the colors properly set. Drag Region on the Level of Detail so that its pill is below Measure Names.
- Click on the invisible Shelf Menu to the left of the Region pill to change it to be a Color pill as well, so both Measure Names and Region are on Color. Now you’ll see a Color Legend that looks like this:
These colors don’t look anything like our Region colors, the reason why we see 8 values is because of the 2 measures and 4 Regions. The goal is to have the Selected Sales measures each match the particular Region’s color, then the Sales – Total Selected Sales measure will all be gray.
- Change the order of pills on the Measure Values so the Selected Measures pill is above the Sales – Total Selected Sales pill.
- Edit the colors, here’s what comes up:
- Change to the Tableau 10 palette, then Assign Palette:This gets us to having the Selected Sales Regions working.
- Next, change the four Sales – Total Selected Sales colors to all be gray:
- Click OK, now the colors are set. You won’t see any change in the view just yet.
- Tableau’s default is to set the Color Transparency on an Area chart to be 60%, if you want the colors to match then click on the Color Shelf button and crank the Transparency back up to 100%.
- On the Measure Values card, move the Selected Sales measure back down to the bottom. This controls where the Area Chart will draw the the Selected Sales, we want them to be on the bottom so the values are more visible.
- Because this is an area chart that stacks marks, we don’t specifically need the sales spacer. However, I wanted to show the Line chart and Area chart side-by-side, so we need to fix the axes for both at the same value. Since the marks will stack for each region, we need to divide them by the total number of regions in the view. Duplicate the Sales Spacer (Line) measure and edit it to add a /TOTAL(COUNTD([Region])), as in:
SUM([Unlinked Region Sample - Superstore - English (Extract)].[Sales])*1.1/TOTAL(COUNTD([Region]))
- Drag the new Sales Spacer (Area) measure over the Sales Spacer (Line) measure that’s still on the Level of Detail Shelf to replace it.
- Set the Compute Using for Sales Spacer (Area) to Region.
- Create an invisible reference line for Sales Spacer (Area):
- Now you can make a dashboard with the area chart, and set up a Filter Action from the Bar Chart to both the Line and Area charts:
Personally, I prefer the area chart view to the line chart because there are fewer changes as the line changes, however if multiple lines are being selected then the line chart works better. A third option would be to use a stacked bar chart, I’ll leave that one as an exercise for the reader. Thanks again to Anthony and Catherine for their ideas and feedback!