Table calculations provide an incredibly powerful functionality within Tableau Desktop, yet often create confusion for both new and experienced Tableau users. To aid in understanding, let’s explore why table calculations are important, define important concepts like addressing vs partitioning, and showcase applied examples using Tableau’s superstore dataset.
What are Tableau Table Calculations?
Table calculations are a special type of calculated field within Tableau Desktop that apply transformations (i.e. additional math) on values within a visualization. Common examples of table calculations include running sum, moving average, and percent of total. Calculations are computed over local data (post-filtered data) within Tableau. The important concepts to keep in mind are:
- Table calculation math will be based only on dimensions (granularity) within the view.
- Local data means calculations occur after all filters are applied.
This means that how you build a visualization, which dimensions are added, and what data is filtered out all play a critically important role in making table calculations work as intended. Thankfully, Tableau has provided easy solutions like quick table calculations. They take a best guess on how you want math like running sum or percent of total to work on your visualization. They are created by right clicking on a measure > ‘Quick Table Calculation’:
To access quick table calculations, right click on any measure within a Tableau view
Much like ‘Show Me’ helps with building introductory Tableau visualizations, quick table calculations are a fantastic segue into more advanced concepts.
Visually, table calculations in Tableau’s interface are denoted by a triangle within the measure’s pill.
To edit existing calculations, right click on the measure pill > ‘Edit Table Calculation…’ to open a powerful, yet often confusing dialog box.
Tableau provides a number of ways to test out preset settings for calculations, and we are going to focus on the last setting – specific dimensions – to master the underlying concepts.
Within ‘specific dimensions’, each dimension in your view has to fall into one of two categories - partitioning and addressing. Tableau uses a list of checkboxes to visually represent partitioning (unchecked) or addressing (checked).
Partitioning is deciding where table calculations start and end. Within Tableau, partitioning is denoted by unchecked checkboxes within the Edit Table Calculation window. Calculations like percent of total require deciding the scope or partition of the math (i.e. what rows need to add up to 100%).
Percent of total sales across categories - partitioned by each category
Percent of total sales across regions - partitioned by each region
Addressing defines the direction of the calculation. Addressing is trickier to understand because it describes what dimensions are referenced (‘compute on the basis of what?’) and the direction they will be used (order matters!). Within Tableau’s user interface, ‘compute using’ is synonymous with setting the ‘addressed’ fields.
In the case of running sum, the addressed region field tells Tableau to sum along region for each (partitioned) category.
For each partitioned category, show the running sum of sales along (addressed) regions
When using ‘specific dimensions’, the ORDER of the ‘checked’ checkboxes MATTERS. Let’s include ship mode and address by Region and then Ship Mode.
For each partitioned category, show the running sum of sales by region, and then ship mode.
Next, let’s look at the same example but swap the addressing (aka direction) of the running sum to be by Ship Mode and then Region.
For each partitioned category, show the running sum of sales by ship mode, and then region.
Luke’s Top Recommendations
With your new understanding of partitioning vs. addressing, creating complex table calculations for your data analysis is more approachable and easier to debug. Here are my top 3 recommendations that have helped in my Tableau learning process.
- Start with tables. I create (and keep) separate worksheets for complex table calculations to help me easily debug and document how the math works. Use the original measures (e.g. sales) in columns next to table calculation columns (e.g. running sum of sales) to check your math.
- Be mindful of performance. Table calculations are super powerful for analysis, but they still need to calculate on local data. Because local data has to be computed on-the-fly by what makes it through worksheet filters, table calculations can be resource-intensive over large data sets and high ‘data mark’ volumes. If visualizations are slow to load, table calculations are near the top of my list to investigate as a culprit.
- Master the ‘Specific Dimensions’ setting. Table calculations are fragile. Because table calculations are based on dimension pills within the view, other Tableau users (or even yourself) can unknowingly break intended functionality. I personally prefer to use ‘specific dimensions’ setting within table calculations to be explicit in the order in which fields are addressed. If new pills are added, Tableau will add to partitioning. Mastering ‘specific dimensions’ provides the best in-tool documentation solution for finicky table calculations.
If you have any questions, feel free to reach out to me on Twitter (@Luke_Komiskey)!