How to Use Table Calculations in Tableau?
Table calculations are extremely powerful tools in Tableau. Not only can they provide additional insights from your data but they can also be used to validate that it is correct. However, they can be lethal to your analysis if not applied properly; think of them as being like a double-edged sword. They’re excellent weapons for any master data analyst to take into the data world battlefield but, with lack of care, you could end up hurting yourself. So know how to use them wisely and you’ll be an unstoppable Tableau Jedi.
In this blog, I summarise how to approach table calculations using techniques that will guarantee that your calculations don’t backfire on you.
First of all…what are Table Calculations?!
Table Calculations are computations that are performed in Tableau on a set of aggregated results that you have queried from your data source (basically what you initially view in your sheet) which takes the structure of an internal table (see image below). There are a number of different types you can use such as Running Totals, Percent of Total, Difference From etc.
Quick Tips on Learning and Building Table Calculations
- When building a table calculation, I’d recommend that you always start your work with data in a table.
- Turn on Total and Subtotal to validate that your data is as expected and is set correctly
- Once satisfied, always anchor in that calculation by editing the table calculations using the SPECIFIC DIMENSIONS. If you use the ‘Table Layout’ options, your calculation is at risk of breaking if you change the layout or structure of your view.
What do I mean by anchor in?
When you create charts containing table calculations, you might tend to drag your table calculation onto the Label shelf for it to display your labels. However, when you then decide to change the table calculation in your columns or rows, the table calculation on your label shelf doesn’t update. Therefore, to avoid your table calculations from breaking, you should always secure them as a permanent selection by choosing SPECIFIC DIMENSIONS every time.
Let’s walk through the three steps above in more detail with an example in which we want to find the percent of total sales for each year by segment using the Sample-Superstore dataset below.
- We’ll start with a simple table where we’re looking at Segment in the rows and Year in the columns. Drag and drop Sales inside the table
- Then hit the Analysis menu, so that you can choose to tick “Show Row Grand Totals” and “Show Column Grand Totals” .
- To then perform the table calculation (in this case, Percent of Total), right click the sales that’s on text shelf, then down to “Quick Table Calculation” and select “Percent of Total”. As shown in the highlighted 100% cells in the image above , we know that the calculation is going down the table. We can also verify this calculation by right-clicking on the Sales mark card again and selecting “Edit Table Calculation”. Here, Tableau nicely highlights how the table calculation is working.
However, note that this is where your calculation might break. This is the point where you should LOCK DOWN your calculation by editing the table calculations using SPECIFIC DIMENSIONS (in other words you want your calculation to be grouped by a particular field that you want to make relative comparisons to).
If you look at the image of our table calculation above, we’ve decided that
- For each Year of Order Date (unticked box)
- Calculate the Percent of Total (Calculation type)
- By Segment (Specific Dimension, ticked box)
- Then we can simply turn this calculation into a chart. Take the table calculated Sales pill and drag it into rows and then the segment into colour. Now we can visualise how everything is calculated. Again, we see as we lasso up each point across the row, they aggregate to 100% which confirms that everything is working by Segment.
Hopefully, this explanation on table calculations gives you an idea on how they work and how to best avoid running into any issues.
The Information Lab Ireland is at the forefront of creating a data-driven culture in Ireland.
As part of its vision, The Information Lab Ireland regularly hosts free events throughout the country to show how being data-driven can improve decision making and lead to a better understanding of the world around us.