Using colour is one of the most powerful ways to bring attention to key details within a visualization. Using big numbers is a favourite within the business world and adding a colour to denote positive or negative change is a quick and easy way to quickly communicate the important information.
I’ll show you a really quick and easy way to show two different colours depending on whether the change compared to a previous year (or whatever metric) is a positive or negative number.
To follow along use go ahead with the Superstore data.
The initial view is as follows with Year of Order Date in the columns shelf and region in the Rows. I’ve simply changed the font size to make the numbers much bigger. I’ve filtered out all years except 2018 and dragged sales onto the text marks card.
Next we are going to right click Sum(Sales) in the marks shelf and ‘add table calculation’.
This is the set-up we’re going with. Essentially we are simply calculating the percent difference from the previous year. When that is set up, drag the Sum(Sales) measure from the marks card and place it on the pane where all the fields are sitting. This will save the table calculation for you. Right click on the calculated field and click edit.
Let’s first rename it to something more meaningful and then this is the calculation we are going to use:
The calculation in simplest terms is saying, if the number returned from the calculation (percent difference from last year) is more than 0 then return the value.
Hit OK, right click the newly created field and duplicate it. Then right click the copied field and edit it. This is going to be for our negative change, go ahead and rename it and this is the calc:
This is the opposite of the last calc. If the number returned is negative, ie: the number compared to the previous year is less this year then return the value of the number.
The premise of these two calculations is that if the number does not meet the condition in either calc then a null value will be returned. We will take advantage of this fact in our labelling.
Drag both of these new calcs onto the labels shelf underneath the SUM(sales) label, click on the ‘Text’ button on the marks button and hit the three dots next to the Text input field.
So now configure the fields however you want them to appear. I have gone for the sum(sales) figure to be nice and big with the two newly created fields underneath. Since this is comparing to previous year I simply have written ‘vs. PY’ next to either field. Since the calculation can only return a positive or negative number, Tableau will bring back the valid value and simply not display the null value.
Just highlight either field and change the colour to whatever you want for positive/negative and hit okay.
There we have it!
I hope this can come in useful, another example of a cool workaround to yield some really valuable results.