Finally we have a use case to do Conditional Formatting and it is a bit of a Hack, when its a double whammy of conditional formatting in a cross tab its a double hack.
Everyone uses the RAG status well when using Excel, and you would think a nice highlight table in Tableau would work great. It sometimes doesn’t – Well not for me anyways. We needed to create conditional formatting for one column, which was fine. so follow the steps below. However someone then asked us “How can I get shapes beside it?” So those steps are too below using the Sales Data that Tableau provides. No hidden sheets, no floating charts – everything in one cross tab that updates nicely together. I’ll show you…
WHO; Myself and colleague @Chris Dunigan.
WHAT; One and Two columns with KPI Measures and shapes.
WHEN; May 17th 2017.
WHERE; A clients swish new office in Ireland.
HOW; Follow the steps below.
ONE COLUMN KPI
Step 1) Drag Sub-Category on to the Rows shelf and drop Sales on to the Text label on the marks card.
Step 2) Write you KPI Colours Calculation – something similar to the below to split out your values based on what your KPI scoring is. For example 1-4 = Low, 5-10 = Medium, 11+ = High.
Step 3) Change the Marks card drop down to a bar, you will see something resemble like the below – don’t worry.
Step 4) Here is the trick – Drag Numbers of Records on to the size button on the marks card.
Right Click on Number of Records > Measure(Sum) > change to Maximum
Click on the size button on the marks card and alter the size of the bar to make it as thick as you like.
Step 5) Drop the KPI Colour Sales Calculation you created on to the colours button on your marks card. Hey presto – conditional formatting.
NB: you can add months, to see the finer breakdown of the calc that you made as it is currently set to every sales in the data set. So everything could be coloured as High at this moment.
TWO COLUMN KPI WITH SHAPES
Step 1) Create a calculated field and call it 0 inside the text box type 0.0. Press ok. Create a second calculated field and call it 1, inside the text box type 1.0. Press ok!
Step 2) Place Category and Sub-Category on to the rows shelf and place Order Date in to the filters box, select Month Year then December 2016. (If using the sales data, following along).
Step 3) Place your new calculations on to the columns shelf. 0 and then 1, right click and change these two pills to a Dimension.
Step 4) Drag your 1 calc on to the size option on 0 marks card, then right click and change the measure of Sum to MAXIMUM. Tableau might not of put the default Gantt Bar on, but you can change this by dropping down the automatic arrow and select Gantt Bar.
Throughout this exercise you will get duplicated marks cards so it is key to stay following along so you don’t get mixed up
Step 5) Next you want to drag your new KPI Colour Sales Calc on to the colour option on your 0 marks card. You will see your bars on the left change colours – note they may not be the correct RAG colour status at this point but we can change that later.
Step 6) Now drop your sales measure on to the text box on your marks card 1
Step 7) Move to the 1 calc pill on you column shelf, right click and select dual axis – given it might not look very pretty yet, just wait.
Step 8) Drag the same calculated fields 0 & 1 on to the column shelf again and turn them in to dimensions buy right clicking > dimension.
Your shelfs should look like the below
Step 9) Take calculation 1 and drop it on to the size option on your 0(2) Marks card, right click and change the measure from sum to maximum. Also change the colour option on your marks card to white.
Step 10) Create a calculated field and call it KPI Sales Shapes. You want to have the same measures as the previous KPI field on the left so the shapes match the direction of the RAG status. I changed from Low, Medium and High to Up, Down and No Change.
Step 11) Click to your 1(2) Marks card and change the automatic drop down to shape so the shape option now appears on the card. Here you need to drop your new KPI Sales Shape calculation on to shape.
Step 12) You can assign shapes to your new measures by selecting shape, default in the new dialogue box that appears, drop the arrow down and select arrows. Scroll through to find the appropriate arrows.
NB: If you are only showing two shape values, put a date filter on for December 2016 so we can limit the data shown to match the calculated fields and see the different colours working.
Step 13) Click on the second 1 pill on your column shelf and select dual axis. Go through each of the marks cards and drag OFF Measure Values.
Step 14) So now you have your dual KPI measures in one cross tab and we want to tidy it up. Bear with me… Right click on the bottom axis and select edit axis. Delete the 0 that is populated in the tittle box, and select NONE both times on the tick marks tab. You will see the bottom axis disappear. You still want to keep the 1 on the top of the axis to name the columns. Repeat this step for the second axis with your shapes keeping the 1 on top.
Step 15) If you haven’t already you can drag the columns smaller in size. And change the colour of your bars to Red, Green, Amber.
Step 16) Right click on the top of the axis, edit axis, and change the title in the general tab to ‘RAG Scores’ (or what ever else you want to name it). Go to the ticks tab and again select NONE for both options. Press ok.
Repeat step 16 for the second 1 axis at the top of your shape column – label this one ‘RAG shape’ or don’t name it at all, just delete the 1. Do press none on the ticks tab.
Step 17) You can format your columns by aligning the text on the 1 marks card and by altering the size of the coloured bars on the 0 marks card
Hopefully you will have something that looks like this below.
To expand further on this, you could turn your shapes measure in to a quick table calculation to show the growth and decline of the previous month. You could add a third axis of received items, against delivered items followed by the %. Instead of the second column being shapes you can add a different KPI measure such as sales against profit.
Credit also goes to the original author who discovered using 1.0 and 0.0 to do this. I watched a quick video, and have now taught this hack in training – hence thought to share this blog.
Let me know how you get on following the steps…