Differential Pricing Using Table Calculations
I’m writing this blog after visiting the Tableau community forum – a place to resolve any doubts. Here is the user’s question I helped solve.
“I need to create a KPI that compares the value for competitors against ours. I am really battling with this. In essence if the number is higher than ours it must be red, if it is lower it must be green and if it is the same it must be black”
This problem is perfect to showcase helper table calculations. If you haven’t heard of them before, they provide the position for a relative item and can be used as an input for other calculations.
We can calculate the difference from the competitors using LOOKUP() and FIRST(). These functions find the target value and the target in the first position. You can easily adapt this technique for example, for the reverse order use LAST(). Find the solution below with the steps required to build the final visualisation.
Here is how I created the highlight table.
Step 1: Add Manufacturer to the Columns shelf, add Region to the Rows shelf, and add Price to Text in the Marks card. Note that sorting order puts ‘My company’ in the first column.
Step 2: Select Quick-Table Calculation > Difference from the right-click drop-down of Price. This creates the initial lookup.
Step 3: Select Relative to > First from the right-click drop-down of the table calculation. This tags first inside the lookup function.
Step 4: Create a calculated field to colour black if the competitor’s value is the same as in the first column, red if the difference is greater and green if the difference is smaller. You can drag and drop the table calculation directly into a new a calculated field, add it to Color in the Marks card, and clear the table calculation of Price.
#Protip: Change the table to a highlight table as the colour will bring out the numbers. add a placeholder calculated field to Size on the Marks card, and swap the mark type from text to bar. Note to add an ‘ad-hoc’ calculated field double-click on the Marks card.
The technique shows you how to get the result using the table calculations menus. As there are multiple ways to do something in Tableau, try writing out the calculation.
