What are Table Calculations?
Table Calculation at its very basic form is a calculated field that applies transformation on measures based on dimension.A dimension contains ‘qualitative values’, so this would be a name, a date or a geographical location. You can use dimensions to categorise, segment and reveal the details in your data. Dimensions will affect the level of details in the view.
A measure contains quantitative values, basically numeric values that you can measure. A measure can be aggregated. When you drag a measure into the view, Tableau automatically applies an aggregation to the measure. What this means is, Tableau by default will set the aggregation to add up the numeric values. If we use Sales for example, it will add up all the value of sales. So if we just drag in Sales, we will get the total sales. If we drag in a dimension, let’s say Dates by Year, then we will get the total sales as at each year.
2 Important Rules
- Table calculations only work on whatever is in your view.
- Table calculations do not consider anything you may have filtered out of your worksheet.
Why Table Calculations?
It’s a really powerful tool that allow you to create additional insights in your data for your client or should you wish to apply for The Data School.
Some of the most common use cases for table calculations are:
- Calculating the Percent of total: This is a good way to see what % of the product contributes to the total.
- Running Total/Moving Average: Instead of seeing the dips and rise in sales for each month within a year of sales, you will instead see a cumulative of sales, so you can see the growth in total across the entire year.
- Difference and Percent of Difference: A simple analysis of seeing how sales or profit has increased or decreased in numbers or % over time.
- Ranking: Ranking sales by dimension to see which sold the most.
Addressing vs Partitioning
In order to understand how Table Calculations works, understanding the theory behind what Tableau is asking you to do will help to make this easier to understand.
When you add a table calculation, you must use all dimensions in the level of detail either for partitioning (scoping) or for addressing (direction). The dimensions that define how to group the calculation (the scope of data it is performed on) are called partitioning fields. The table calculation is performed separately within each partition.The remaining dimensions, upon which the table calculation is performed, are called addressing fields, and determine the direction of the calculation.
Calculating using Specific Dimensions
Here at the Data School, we learn how to use Specific Dimensions following Andy Kriebel’s example which we call ‘Andy Kriebel’s Mantra’. It goes by ‘For each [Unticked Dimension] Calculate the [Table Calculation] by [Ticked Dimensions].
By using this mantra, we can create more advanced table calculations. One example would be a Pareto chart that follows the 80/20 principle. A Pareto chart uses a combination of a Running Total and a Percent of Total table calculation to create. If you are interested in how to create a Pareto Chart, please visit my colleague’s blog as he provides a GIF Tutorial on how to build one.
