#### Demystifying Data Analytics for SMBs

If there’s one problem that most businesses don’t have, it’s a lack of data. In fact, the amount of data in the world is expected to surpass 50...

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.

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.

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.

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.**Start with tables**.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.**Be mindful of performance**.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.**Master the ‘Specific Dimensions’ setting**.

If you have any questions, feel free to reach out to me on Twitter (@Luke_Komiskey)!

Luke Komiskey: Feb 23, 2024

If there’s one problem that most businesses don’t have, it’s a lack of data. In fact, the amount of data in the world is expected to surpass 50...

Luke Komiskey: Feb 19, 2024

Why should managed analytics be a cornerstone of your data strategy? Companies face increasingly growing, complex data challenges, yet harnessing...

Luke Komiskey: Aug 26, 2023

Updated with the August 2023 Tableau pricing changes The ability to efficiently visualize and analyze data is essential for businesses of all sizes,...