This week’s #MakeoverMonday was looking at the phasing out of coal-power in the UK. I really liked the original visualisation that was to be made-over, which appeared in The Guardian online edition (https://www.theguardian.com/environment/ng-interactive/2019/may/25/the-power-switch-tracking-britains-record-coal-free-run).

#### What I liked about the original

The graphic is quite striking and captured my attention. It’s clear to see that there is higher coal usage during the winter months with the lighter areas in the middle (over summer) and darker towards the edges of the chart (over the winter months). The granularity is at the day level, so individual days can be seen, with ‘coal-free’ days highlighted in green.

#### Elements I wanted to change

There is no hover-over interaction with the chart on the website, so it’s not possible to see the exact figures per day. If you are not focused on seeing coal-free days, the green colour can be a bit distracting. This is more of a personal preference but I would move the years to be to the left of the chart data and not overlapping the data.

#### Redesign

For the #MakeoverMonday challenge the same coal usage data was provided along with usage data for seven other energy types, all for energy production in the UK. I thought it would be interesting to create a small multiples chart using a similar design to The Guardian’s visual but showing all energy types to allow comparisons between them.

Below is the final chart I created:

#### Colour Palette

I used the Inferno colour palette as the predominance of oranges made me think of fire and energy.

#### Small Multiples Calculations

To create the small multiples or ‘trellis’ chart in Tableau I created **Row **and **Col** (column) calculations, both of which are table calculations (due to the use of INDEX()) that are calculated along the dimension I want to have in each square – **Energy Type** in this case:

#### How do these calculations work?

Let’s look at the **Index** bit first. This assigns an incremental number, starting from 1, to each of the members of the dimension I specify (Energy Type), which results in these assignments:

- Biomass
- CCGT
- Coal
- Hydro
- Nuclear
- Pumped
- Solar
- Wind

#### Row Calculation

For the **Row** calculation, 1 is subtracted from the Index value and the result is then divided by 3 (the number of columns I want). That result, which could be a decimal number, is then rounded down to the lowest integer (whole number) using the **INT** function.

#### Column Calculation

The **Col **calculation is a little trickier as it use the **modulo **operator, which (and I’ll quote from Tableau’s online help here) “…returns the remainder of a division operation. For example, 9 % 2 returns 1 because 2 goes into 9 four times with a remainder of 1”.

In the diagram below I’ve recreated these calculations in Excel and shown how they map out to their resulting rows and columns thus creating a grid of 3×3 with the lower right square being empty as we only have 8 items.

Note the modulo function looks different in Excel. Instead of** 9 % 2**, it uses the format: **MOD(9, 2)**.

This results in the grid below for the Energy Types:

#### Year Headers

When I showed all the years on the left of the chart it felt a bit cluttered, so to remedy this I created a calculation to only show the first and last years in the data set: 2012 and 2019.

The final view setup is shown below:

The small multiples’ calculations can get more complex when you have multiple dimensions in the view and especially if some of your dimensions have null values. For more detail on how to handle these cases, see the resources at the end of this post.

As there are nine squares in the grid and only eight Energy Types, I decided to use the ninth square to show a key for explaining which Energy Type was visualised in which square of the grid. Within the key there was also one empty space, of course, so I used that to put the colour legend:

#### Small Multiples Line Charts

To aid in understanding of the data and to add something new to the original design, I created another small multiples using more conventional line charts that aggregated the data to the month level. The same row and column calculations are used, and the view setup is shown below:

I’ve used a dual axis to be able to have an area chart with a line chart on top to emphasise the top of the area. There is a filter in place to remove the final mark as it was a part-month so wasn’t comparable to all other months. The formula: ** LAST()<>0** ensures the final mark, being calculated across the dates, is not shown:

Finally, I added tooltips to be able to see the day/month exact figures on each chart and supporting text to explain the charts and narrative as a whole.

#### The Final Visualisation

Click the image to open the interactive visualisation on Tableau Public:

#### Small Multiples Resources

As mentioned earlier, small multiples can get quite complex with many dimension and null values in your data. The below blogs and videos are great resources that give more details in how to handle these situations:

- Why won’t my Tableau small multiples chart work? by Gwilym Lockwood
- Small Multiples with Index in Tableau – Solving Common Issues by Chris Love

Thanks,

Marc

## Leave a Reply