The final week of #MakeoverMonday in 2016 looked at data from the BBC related to the rising costs of Christmas dinner in England. I’m going through a spell of concentrating on understanding the construction of “corporate” styled dashboards as these are the ones I need to create at work, and a dashboard from Andy Kriebel was worth looking at this week.

As usual, I started to recreate the viz in Tableau Public, with Andy’s version open separately in the 10.2 Beta version of Tableau. I always aim to try to rebuild the dashboard as best I can without referring directly to the original, other than to look at the dashboard sheet itself to understand the basic structure of things.

After importing and Pivoting the data, and setting the default number formatting, I started to build:

Looks easy enough? The [2016 Cost] calculation is simply:

I set the number formatting to Currency, made it Discrete and dragged it alongside Product Family on Rows. I also referred to this new calculated field when setting the Sort order:

Now it gets fiddly. The % Change value represents the change in Cost from 2006 to 2016. I tried this a few ways. First, I duplicated the [2016 Cost] calc to create a [2006 Cost] equivalent, and then I wrote this:

It didn’t work:

Because of the way the data is structured, and the way those calculations work:

As shown above, where the datapoint is 2006, then the [2006 Cost] calculation returns a value, but the [2016 Cost] calculation does not. Conversely, when the datapoint is 2016:

A bit of a silly mistake to make. How do I fix this? Well, I created a new sheet and used the innate Table Calculation functionality to write a Percent Difference From calculation, Relative to First:

I can use this to sense check Andy’s 3.3% Change at a Total Cost level:

Good, I have something that reconciles to the original visualisation. Now to figure out how to force this calc into my view. I drag the calc to Measures and rename it % Change, and then look at the calc Tableau wrote for me:

It’s a bit long-winded, but in summary it takes the current cost and subtracts the FIRST() cost in the Row (i.e. the 2006 value). The product of that is divided by the FIRST() cost in the row. The issue I have is that first section which refers dynamically to a current cost value, rather than being fixed specifically on 2016. I need to incorporate the LAST() function somehow:

I’ll admit there was some fiddling with brackets, but it looks about right. What happens if I plot that on the same chart?

Another tick in the box! The [% Change] calculation is working fine, so now I should just need to make it Discrete and move it onto Rows of Sheet 1, right?

Wrong! The reason is that Table Calcs need to have a relevant Compute Using field set. By default, they set to Table Down, which doesn’t make sense with this view. What I need to do is introduce Years into the scope of the view, so I can set Compute Using to Year.

Splendid! Another issue to resolve. See how the [2016 Cost] field is giving me trouble? This is an extension of the issue highlighted earlier. Where the datapoint is not 2016, then a Null line is returned. I need to ensure that just one Row per Product Family is returned. Time to rework that [2016 Cost] calculation:

With that converted to Discrete, plonked on Rows, we have progress. Converting Year to Continuous and dragging Cost to Rows achieves this:

They don’t look like sparklines yet, but that’s just basic formatting and is just a case of setting individual axis ranges and unticking “Include zero” from the edit axis menu. Then you can hide the header, clean up gridlines and shrink the horizontal space taken up by the chart:

A fair amount of the viz is now complete. Now it’s time to figure out the secondary axis. On it, Andy plotted the [2016 Cost] as a dot at the end of the line (coloured to denote whether it had increased or decreased in cost since 2006), plus the Maximum Cost for each Product Family.

First of all, I’ll write a calculation to figure out the Maximum cost. It’s a simple WINDOW_MAX:

As is almost always the case, I’ve gone into the calc a little too simplistically. Look what happens if I plot it as a dual axis:

The orange line is a continuous representation of the WINDOW_MAX. The calc needs refinement to only show the WINDOW_MAX for the relevant date:

A couple of colour tweaks, and we get here:

On the right track, but I need to add a dot to the end of each line using the [2016 Cost] calculation. I know I want that to be plotted as a Circle on the secondary axis too, but don’t actually know the most efficient way of doing it. What I usually do is select the second axis, then drag Measure Names to Filters and select the Measures I want to chart:

That’s better, we now have this:

Currently the [Max Cost] calc is always red, and the [2016 Cost] is always blue. The final viz has the [Max Cost] as blue, and then the [2016 Cost] either as red or turquoise, depending on whether the cost had increased or decreased since 2006. Another challenge, and one that I’ll overcome with my semi-understanding of those useful FIRST() and LAST() functions:

Only in cases where the Year is 2016 will the boolean aspect of the calculation be applied, else the calculation will return null. That allows me three colour options, so I used blue for Max, red for cases where 2016 is higher than 2006, and Green for cases where the 2016 cost is lower than 2006.

Finally, I’ve added a boolean colour calculation to the primary axis to show the Total Costs as a darker line than the disaggregated Product Family costs.

I just copied and pasted Andy’s title and haven’t focused on tooltips at all (maybe they will for part of a New Year’s Resolution…)

My final version is available to view here.