One of the biggest challenges Tableau newcomers face is lack of standard features available in Excel. Conditional formatting is one to name such. Recently good news surfaced that this feature is on shortlist to be added to 10.2 version but I believe that part of current user will not be able to migrate to higher version in short time.
However following couple discussions on the community forum, it seems that it’s possible to mimic conditional formatting in Tableau using few tricks / hacks.
There are two main approaches to implement color coding – Multiple dual axes technique and Scaffolding technique. First one allows you to format whole columns and second one brings additional functionality to color single mark / row. Both methods will be explained using EU Superstore Sample dataset.
Multiple dual axes
Inspiration for this approach was awesome post by Jonathan Drummey, who gathered multiple ways of different conditional formatting that could be applied to a cell. You can access original version here: <Click me>. Although very colorful and impressive, this approach lack one of the functionalities – sorting. My approach mitigates this limitation by doing one small change.
General idea in multiple axes approach is to dual axis consist of one axis containing small chart colored by Calculated field and one keeping values and sorting.
Let’s create simple coloring sequence to see which region is below or above selected orders value. Threshold will be applied using parameter, however it can be hard coded in calculated field as well. Place Region field on the Rows shelf.
And Calculated field which will be used for coloring:
if COUNTD([Order ID])>=[ThresholdParam] then 1 else 0 end
Change this field to discrete for better color control
Now start typing on the column shelf. Type 1. You should have SUM(1) as a green pill.
Having SUM setup automatically here is crucial. If your value stays as ‘1’ then most likely you have Aggregate Measured option disabled. Navigate to Analysis> Aggregate Measures and enable it.
Second important thing is to change
MIN(1). This way we will have only single data point on axis not multiple one for each row. This change is base for most of the tricks/hacks made using dual axis in Tableau.
Having first axis setup, let’s pull count
COUNTD([Order ID]) as second green pill to the Columns Shelf. Right click on it and select Dual Axis.
Now let’s setup our values and coloring:
- On the Marks Shelf select
MIN(1)tab and double click somewhere on the tab and type 1 – this should change to
SUM(1). Change aggregation method to Minimum so you have
MIN(1)and pull it to Size
- Pull Order ID as
COUNTD([Order ID])to the Label
- Pull Threshold field to color
- Change Mark type to Gantt Bar
Your tabs should look like this:
Let’s fine tune it further:
- Click on the top Axis, select Edit Axis and Switch Range to Fixed. Change Start and End Values to 1 and 2
- Click on the bottom Axis, select Edit Axis and Switch Range to Fixed. Change Start and End Values to 1 and 2
[click for gif]
- Click on the bottom Axis, select Edit Axis, clear everything in Title. Go to Tick Marks Tab and select None for both Major and minor tick marks
- Click on the top Axis, select Edit Axis, change Title to Orders Count. Go to Tick Marks tab and select None for both Major and minor tick marks
- Click MIN Tab in Marks Section, select Color, Edit color, click 0 value and assign Red color to it, click 1 value and assign Green value, select apply. Be careful and check if colors are still as you expect after publishing. Sometimes color can change, for solution head to blog post: http://serwer1996697.home.pl/meowbi/2016/10/27/shapes-out-of-shape/
- Arrange cell size using sliders to make this looks like a single cells.
And Voila – you’ve created your first conditional formatting based on Order Count! You can now bring up parameter control, change threshold values and see how coloring will change. Also note that sorting ability is kept, so you can sort regions by colored measure.
If you would like to add next cell then simply pull another
MIN(1) and Measure to the Column shelf as dual axis and repeat steps.
Scaffolding technique is bit more tricky as it requires deeper understanding of how Tableau works and visualize data.
Coloring technique was presented by long term Tableau Professional Joe Mako as part of general Scaffolding introduction here: https://vimeo.com/69131084.
Scaffolding technique can also be used for creating single date selector controlling different date fields.
In the first example, you could color whole column as one. Scaffolding technique gives you more control over conditional formatting, specifically, brings the ability to color each value independently as it’s treated as separate Mark. Please note that this method uses semi-manual data processing. This means that it requires input from non-standard Tableau routines and therefore it would make it less dynamic. However with help of ELT or Custom SQL Script would allow this solution to work almost seamlessly.
OrderCount field is a Calculated Field defined as:
Let create a scaffold which will be used to map any of the Measure and region combination.
This is the moment where automation should be implemented. If any new region / measure is added then this technique will not work. This additional datasource can be saved in any format Tableau will be able to read. I choose .csv as this is my general preference for such purposes. Import this source to Tableau.
If you pull Region to Rows Shelf and Measure to Column Shelf you notice that Order count, profit and Sales are actually one Measure and therefore can be addressed directly.
When You click on the Superstore datasource (the one holding real values) and scroll down, you will notice that Region field (which is common in both datasources) is linked with the new source – chain icon visible next to field name
Unfortunately pulling any of the values from main source will result in showing same figures in orderCount, Profit and Sales columns. We need to create calculated field which will display proper values in each of the columns. Note that this new field, should be created in secondary datasource (however it will refer to the main source)
case min([Measure]) when "OrderCount" then [Sample - EU Superstore].[OrderCount] when "Profit" then sum([Sample - EU Superstore].[Profit]) when "Sales" then sum([Sample - EU Superstore].[Sales]) END
See, there’s a reference to Sample – EU Superstore datasource. Note that OrderCount field is not aggregated here as it’s already aggregated (it’s COUNTD). Although MIN([Measure]) might make no sense, it’s required that all elements of case statement are aggregated. Measure is a Measure type so it will make no difference if we add MIN() aggregation. Pulling Value Field over sheet now produces proper values
Let’s color! With this very custom approach, we can color both single column and row. Create 2 fields, both of them discrete for ease of coloring:
if min([Measure]) = "Profit" and SUM([Sample - EU Superstore].[Profit])>=[ThresholdParam] then 2 elseif min([Measure]) = "Profit" and SUM([Sample - EU Superstore].[Profit])<[ThresholdParam] then -2 else 0 end
if min([Region]) = "North" and [Value] >= [ThresholdParam] then 2 elseif min([Region]) = "North" and [Value] <[ThresholdParam] then -2 else 0 end
- For single column coloring pull ColorProfit to Color Mark on Marks Shelf, click color, edit Colors. Then assign 0 with neutral, dark color, -2 with red-ish and 2 with green-ish
- For single row coloring pull ColorProfit to Color Mark on Marks Shelf, click color, edit Colors. Then assign 0 with neutral, dark color, -2 with red-ish and 2 with green-ish
Tableau implemented feature allowing you to implement conditional formatting without workarounds.. Sort of..
You can setup multiple color legends for each of the measure, but unfortunately you cannot use different calculated field to base conditional formatting evaluation on. It means that, for example, you can color Sales and Quantity using different colors but you can’t color Sales and Quantity using profit..