Conditional Formatting in Tableau Using Data Blending
Generally conditional formatting is easy to implement in Tableau writing IF statements. The complexity goes high when we have more conditions and combinations and we may end up writing higher number of IF statements. Also its difficult to maintain and modify whenever there is a need or requirement change.
The below article articulates to achieve the conditional formatting though we have more conditions and combinations.
Dataset
Below is the sample transnational data which tableau will connect to draw analytics and build dashboards. Variance is a calculated field inside tableau. Here variance is nothing but difference between Actuals Vs Forecast and the calculation as like as (([Actuals-Forecast])/[Actuals]).
Conditional Formatting Data (Target Metrics):
By tolerance, we mean that say for India, target is 6.5% or -6.5%. If the score is within this range, we color the result as Green. If the score is between either 6.5% and 7.5% (since tolerance is 1%) OR -6.5% and -7.5%, we color the result as Yellow. If the score is beyond either 7.5% or -7.5%, we color the result as Red.
Required Output
Conditional Formatting condition: In above view, there would be a filter for the Category. Which means if user clicks on Ice Cream, then the India variance result will become -11.1% and it will be colored RED, against the target and tolerance for India Ice Cream +/-5% and 1%.
Non-optimal Solution
When we write conditional formatting logic in tableau say user selects Ice Cream and Ice Cream has different target numbers for different countries hence, we end up writing a combination of COUNTRY & CATEGORY. Ex: INDIA AND ICE CREAM, USA AND ICE CREAM, AUS AND ICE CREAM and so on. The same we will have to write for all categories.
However, there are problems in above approach:
- If countries and categories are growing, then my calculation need to get more lines which is a major hit for performance and very difficult to maintain
- In current example, I wrote for combination of Country and Category but in actual scenario we may get many more dimension to add then the code will become huge
- Suppose after few quarters or years if there is any target or tolerance metric gets reviewed then its bit difficult to update in the code
Optimal solution — Data Blending
“Data blending is a method for combining data that supplements a table of data from one data source with columns of data from another data source.”
This category table is created from target file for reference to mark - green numbers are upper limit and lower limits, amber tells us tolerance limits and beyond tolerance value will be treated as Red. Using this reference table, below table is created in a structured format which indicates UL, LL and tolerance for each country and category accordingly. This is used to blend with transnational data.
This is the table is used as an additional data source to tableau to create the conditional formatting.
After connecting the Transnational Data as Primary Data Source and Target Sheet as Secondary Data Source I have established a relation between these two data sources. Now, created a calculated field as shown below:
Above is the formula to get the expected conditional formatting from data blending. Below is some explanation:
Highlight in Green: This condition checks distinct count of category from DB(used ‘{}’) Vs distinct count of selected values in dashboard filter. The only case this condition satisfies when user selects ‘(All)’ option in dashboard filter; else if user selects any ONE particular category then this condition will not satisfy and lookup goes to 2nd green highlight to execute given condition.
In simple terms if user selects ‘(All)’ in dashboard filter then user gets country level color condition which is 1st purple color formula else gets 2nd purple color formula.
Let us assume user selects ‘(All)’ from dashboard filter and means 1st GREEN CONDITION is satisfied and GREY color formula matches the condition to execute purple color formula where user gets COUNTRY level color coding:
If user selects ‘Ice Cream’ from dashboard filter and means 2nd GREEN ELSEIF formula matches the condition to execute purple color formula where user gets CATEGORY level color coding:
Watch the below video clip to understand better:
This advance technique makes conditional formatting much more efficient. Try it in your work. Let me know if you face any issue.
