Day 16: KPIs from Table Calcs

You didn't think we'd get through 30 days without talking about Table Calcs did you?  Table Calcs are like Tableau's black box - very powerful, but often misunderstood.  It's one of the quickest ways to discover trends in the data.  The list of built in capabilities and ways to look in to the data is just awesome (we'll show the full list in a minute).  Another thing we talk about all the time with clients is the ability to have the 20 second view of key metrics, often called Key Performance Indicators (KPIs).  Today we'll look at how to create a table calculation and then leverage it to create a KPI.

Learn About Tableau

Before we talk about how to do this in one particular way, let's start with all the different table calc options Tableau makes available.  The way to see this is to place a date field on columns and a measure on the text shelf, right click the measure, and hover over Quick Table Calculation and you'll see all these:

All of these options are really useful in the right circumstance.  Today we're going to look at Year over Year Growth of Profit (from our favorite, Superstore Dataset).  Let's get going.  The set up is really simple - start with Order Date on columns and throw Profit on to the text shelf.  Should look like this:

So that we don't lose track of where we are from a raw numbers perspective we're going to bring Profit back in a second time - put it on details and then right click and go down to Quick Table Calculation>>Year over Year Growth.  Now click on the white space to the left of it and add it also to the text self.  You should now have two rows of the exact same Profit data and should now see this:

We can look and do some mental math and see that this looks spot on.  The cool thing is that we can actual dig into this down to the quarter, month, week... level by simply marching down the date hierarchy. I'm going to take it down one level to quarter and see that this still works:

Not what you expected?  This is like Q1/Q1, similar to the way public companies report earning.  Not what you wanted?  You can change the way the table calc works.  Right click on that pill and hit Edit Table Calculation and you get this box:

To get the quarter by quarter view you we expecting just change the level to Quarter of Order Date.  Change it and you get this:

Now for the trick - here's the easy way to create a KPI from this.Right click and go back to edit the table calc.  On the bottom left you see a button that says Customize - click it and it brings up the calculated field that Tableau worte in order to produce the table calculation you saw.  Here's the fun part.  Copy it and kill the window.  Create a new calculated field and paste it in there.

At the end of the calculation add ">0".  Now, write an if statement, with the formula as the boolean, followed by "then "Up" else "Down" end".  The whole thing should look like this:

This field will now go on to the shape shelf (one you change the mark type to shapes).  Click on the shape legend and take advantage of the KPI shapes that are already in Tableau.  I went with green circles and red diamonds.

After a little formatting we get this little table:

If we hid the bottom row, we'd have the beginnings of a pretty sharp KPI dashboard.  And now you know how to make it happen.

Learn About Tableau

Thanks for playing along!

Nelson