Let’s say that you wanted to have two measure values on one graph and to colour by these measures as well as have lines with different thickness. Prior to last week I thought it would be necessary to use a dual-axis chart for this which was a frustration as this means your axis is tied up and you can’t use it for anything else. However, it is possible to use one axis for multiple measures with independent colouring and size. If you want to follow along with this step-by-step then I have made this example using sample superstore. You may notice the colours are different for the first few stages to those in the final chart pictured below. Don’t worry about this. Tableau will automatically change the colours in the final step.
Step one: Build a simple line chart by right-clicking and dragging order date onto the column shelf. Select continuous month (order date) from the dialogue box that will appear and then double-click profit to automatically add it to the rows shelf. You can then colour this by region by dragging the region dimension onto the colour section of the marks card. You will end up with something that looks like this:
Step two: Next, we will add the moving average of profit. You can do this by holding down the ctrl key and clicking on the profit pill already on the rows shelf and dragging profit to the right side of the existing pill. This will duplicate the profit pill and give you two graphs of sum of profit against month of order date. You can then add the moving average of profit by right-clicking on the right-hand profit pill and selecting add table calculation. Click the drop down underneath calculation type and select moving calculation. The dialogue box will now have another drop down beneath moving calculation that reads sum, prev 2, next 0 [Image 3].
Click the carrot to the right of this drop down and then, in the resulting dialogue box, click the carrot to the right of sum and select average. The calculation that I want to perform is a 6-month moving average and so the previous values section of this dialogue box needs to be changed to 5. If you are wondering why this is the case when we are trying to calculate the 6-month moving average: the current value box is ticked. This means that the
previous value drop down should take the value of N-1 months as the calculation works point to point and will include the current point and the N points previous to it. This also means that when applying this calculation to any other data sets, care should be taken to check that a data point exists for each month, week etc or whatever granularity you are performing your moving calculation at. Once you have done that you can click back on the white space of the table calculation dialogue box which will close the summarise values box. Then, in the compute using section of the table calculation dialogue box, select specific dimensions [Image 4]. This ‘fixes’ the table calculation set-up so that calculation direction won’t change if dimension pills are swapped over etc. You will see that the month of order date box is ticked whilst the region box is unchecked.
The way to read this dialogue box is that it is calculating the 6-month moving average of profit for each region, moving along the date field. For more information on how to read the dialogue box for table calculations see Andy Kriebel’s great blog post.
If you close the dialogue box then you should now have a plot of the 6-month moving average of profit for each region for the bottom graph and a plot of the sum of profit for each region for the top graph [Image 5].
Step three: Click on the right hand pill for sum of profit (you will note a triangle has appeared on this pill indicating that it is now a table calculation) and drag it down to the profit x-axis. A double ruler sign should appear to show you are making a combined-axis chart. Drop the pill on the x-axis when you see the double ruler appear. Your chart should now look like this:
Step four: Drag the blue measure names pill off the rows shelf and place it on the size box of the marks card. You will then be left with one chart that looks like the one below [Image 7]. It is getting there but it still doesn’t look quite right. We want the moving average line to be the thicker of the two with the profit lines in the background. We also want to colour by both measure names and region.
Step five: Click the carrot at the top right of the measure names legend (to the right of the worksheet) and select edit sizes. In the resulting dialogue box check the box next to reversed and then make the profit lines smaller by dragging the left hand toggle of the mark size range option further to the left (towards smallest) to reduce the range starting size. You can then change the thickness of the moving average lines by dragging the right hand toggle further right [Image 8]. You can make changes and hit apply to check the size range. Once you are happy with your settings click OK. Now all that remains is to colour by moving average and profit in addition to region.
Step six: Hold down ctrl and click the measure names pill on the marks card to replicate the discrete pill and drag it onto the detail box of the marks card. Then click the detail dots next to measure names and on the drop down that appears select colour. Tableau will automatically change the colours so that you have a darker colour for the moving average and a lighter tone of that colour for the profit line corresponding to the same region. You can easily change these by double-clicking anywhere in the colour legend. All that remains is to format the chart (here I have removed the grid lines) and you now have a chart coloured by both measure values and region with an axis free for further fun!
Featured Image: Photo by Emily Morter on Unsplash