It’s easy in Excel. So it should be easy in Tableau right? Well, not quite.
There’s a little trickery involved! But we likes that, don’t we my precious…
I have 200 unique regions in my dataset so I wanted the chart to show a range from the min to the max. Then I wanted to overlay an average line and a specific, selected region.
I looked everywhere but couldn’t find a tutorial detailing how to do this. This excellent post came close, but it doesn’t allow multiple lines since you can’t have multiple instances of multiple measure values. Then I read this forum post and almost dropped the idea, thinking it was impossible. But a search through the Tableau Zen Masters public galleries led to me to this particular chart from Joe Mako, exactly what I was looking for!
I downloaded it, played around with it and then implemented my own version. The clever part is that it uses a polygon to create the banding.
For future reference, these are the steps I followed:
Multi-line chart with custom range banding in Tableau
1. Setup your data in Excel first.
My dataset consisted of a list of regions, each with a price listed per year, ranging from 2007 to 2015. The data was stacked in a tall table format. The second column, Type, was added so that I can classify “Regions” and “Bands” separately.
At the bottom of the dataset, add additional rows for the banding entries. Leave the Region column blank, tag them as Band Min or Band Max (one of each for each year) and add the min and max values from all the regions for that year, as follows:
To find the minimum value per year excluding the zero’s (my dataset had many zero’s), use a handy small formula:
where the range D2:D201 contains all the prices for 2007 in my dataset. It’ll need adjusting for the other years, 2008, 2009 etc.
Quick aside: In his worksheet, Joe Mako used UNIONs to place all of his data into one table, stacked on top of each other. This feature was introduced in Tableau 9.3. Since I’m still on 9.2 (yes, I know, weekend project to update!) and since I want to focus on Tableau here, not SQL, we’ll do this step manually in Excel. Look out for a future post on this.
2. Open Tableau and connect to this Excel file. Select the tab with your data and open a new sheet.
3. Create the following Calculated Fields:
This creates a new measure (column) where all the Region rows are NULL but all of the Band Rows (min and max) have the price recorded.
This does the opposite – it creates a new measure where all the Region rows have their price recorded but all of the Band rows are NULL.
4. Drag Year onto the Rows shelf and Band Price onto the column shelf. Tableau will aggregate Band Price using SUM, which is fine. This gives us a simple line chart, no surprises there.
At the moment, the line represents the sum of the Band Min and Band Max values (e.g. the 2007 value of the line is 716 which is 126 (min) + 590 (max)).
5. Create another new Calculated Field
What this does is create a new measure where the Band Min rows are labelled with negative year numbers, but the Band Max rows are labelled with a positive year number.
Now drag this measure onto the Marks shelf and change to a dimension and you’ll see the points for each year be separated out into min and max, as we want.
Then select Path from the dropdown menu next to the Polygon Order on the Marks Shelf.
The path joins the dots in the correct order because it starts from negative 2015 through to negative 2007, then onto positive 2007 to positive 2015. If I add the Polygon Order field as a label in the Marks shelf and change it to a dimension, then I think it’ll make sense:
Now if you want to be really explicit with the order and you can forgive this verbose IF statement, then try this as your Polygon Order field instead:
IF [Type] = "Band Min" AND [Year]=2007 then 9
ELSEIF [Type] = "Band Min" AND [Year]=2008 then 8
ELSEIF [Type] = "Band Min" AND [Year]=2009 then 7
ELSEIF [Type] = "Band Min" AND [Year]=2010 then 6
ELSEIF [Type] = "Band Min" AND [Year]=2011 then 5
ELSEIF [Type] = "Band Min" AND [Year]=2012 then 4
ELSEIF [Type] = "Band Min" AND [Year]=2013 then 3
ELSEIF [Type] = "Band Min" AND [Year]=2014 then 2
ELSEIF [Type] = "Band Min" AND [Year]=2015 then 1
ELSEIF [Type] = "Band Max" AND [Year]=2007 then 10
ELSEIF [Type] = "Band Max" AND [Year]=2008 then 11
ELSEIF [Type] = "Band Max" AND [Year]=2009 then 12
ELSEIF [Type] = "Band Max" AND [Year]=2010 then 13
ELSEIF [Type] = "Band Max" AND [Year]=2011 then 14
ELSEIF [Type] = "Band Max" AND [Year]=2012 then 15
ELSEIF [Type] = "Band Max" AND [Year]=2013 then 16
ELSEIF [Type] = "Band Max" AND [Year]=2014 then 17
ELSEIF [Type] = "Band Max" AND [Year]=2015 then 18
Of course, you should change that IF nonsense back to the concise formula:
6. Change the chart from a line to polygon, in the Marks shelf. Then remove the labels and change the color to light grey:
7. Drag the Price measure to the secondary axis. Change from SUM to AVG. Remove the Polygon Order from the Mark pane. Change from polygon to line. Finally right click the secondary axis and synchronize with the first axis. The following GIF shows these steps:
8. Create a new parameter called “Selected Region” with datatype “String” and make it a list of the Region names:
9. Add a new calculated field for showing the region that is selected, as follows:
Show Selected Region Price
IF [Selected Region]=[Region]
10. Drag this new calculated measure onto the secondary axis. Your chart should look something like this now (my data had lots of zeros!):
Boom! You’re essentially done. There’s some tidying left to do and setting up the dashboard with the Region filter but that’s it.
11. Tidy up the sheet by hiding the second axis. Adjust the Year axis to run from 2007 to 2015 so that our bands run to the edges of our chart. Rename the other axes, add titles, change colors if you want.
12. Create a new dashboard and add the “Multi-line with bands” sheet.
13. Click the top right arrow of this sheet and add a quick filter for the calculated parameter “Selected Region”. This will give you the desired multi-line, dynamic chart with bands.
Click here to see the live version on Tableau.
Any questions, leave a comment or send me an email. Thanks!