Tableau Table Calculations 101: Functions & Examples

Table Calculations are an incredibly powerful feature of Tableau Desktop, but they frequently confuse both new and experienced Tableau users. To help you understand, let’s look at why Tableau Table Calculations are important, define key concepts like addressing, and partitioning, and show how they’re used with Tableau’s superstore dataset.

Tableau Table Calculations are a subset of Calculated fields in Tableau Desktop that perform transformations on values in a Visualization. Table calculations commonly include running sums, moving averages, and percentages of totals. Table Calculations in Tableau are performed on local data (post-filtered data).

Tableau Table Calculation concepts will be covered, as well as how to create and edit a calculated field. You’ll also learn how to use a Calculated field in a view.

This is a good place to start if you’re new to Table Calculations in Tableau or Creating Calculated fields in Tableau.

Prerequisites

• Tableau Desktop.
• Basic Data and Visualization Understanding.

Introduction to Tableau

Tableau is a Data Visualization and Business Intelligence platform founded in 2003 by Christian Chabot, Pat Hanrahan, and Chris Stolte. It grew in popularity because every organization wanted to gather valuable insights from multiple data sources while also collaborating with their employees. Visualization is an excellent method for analyzing large amounts of data, and Tableau excels at it.

Tableau has assisted leading organizations across industries in reducing processing time and becoming more data-driven, all while ensuring Flexibility, Security, and Reliability in all of their processes.

Key Features of Tableau

Tableau has more features than other BI tools, making it a better choice. Here are a few examples:

• It has a Large number of Integrations from which to choose.
• Unique drag-and-drop feature.
• It turns your questions or queries into Visual Representations.
• Tableau can be accessed via any platform, including Mobile, Web, and Desktop.
• It allows you to create a diverse set of Visualizations to help you explore your data.
• Tableau has over 200 connectors that allow users to securely connect to external data sources such as RDBMS, Cloud, spreadsheets, and so on.

What Exactly are Tableau Table Calculations?

A Tableau Table Calculation is a transformation that is performed on the values in a Visualization. Tableau Calculations are a subset of calculated fields that compute the local data in Tableau.

Table Calculations can be used for several different things, including:

• Converting values into rankings
• Changing values to display running totals
• Changing values to show the percentage of total

A Virtual Table is determined by the dimensions in the view for any Tableau Visualization. This table differs from the tables in your data source. Typically, the dimensions within the detail level determine the Virtual Table. This means that how Visualization is built, which dimensions are added, and what data is filtered out all play a critical role in ensuring that Table Calculations work as intended.

Also, It has provided simple solutions such as Quick Table Calculations in Tableau. They make an educated guess as to how you want math, such as running sum or percentage of the total, to work on your Visualization. They are made by right-clicking on a measure and selecting ‘Quick Table Calculation‘

Tableau Table Calculations are represented visually in Tableau’s interface by a triangle within the pill of the measure.

Tableau provides many ways to test out calculated preset settings, and we will focus on the last setting – Specific Dimensions – to master the underlying concepts.

Each dimension in your view must fall into one of two categories within specific dimensions: Partitioning or Addressing. Tableau visualizes Partitioning (unchecked) or Addressing using a list of checkboxes (checked).

1) Partitioning

Partitioning is the process of determining where Table Calculations begin and end. Partitioning is indicated in Tableau by unchecked checkboxes in the Edit Table Calculation window. Calculations such as percent of total necessitate determining the scope or partition of the math (i.e. what rows need to add up to 100 percent).

2) Addressing

The Calculation’s direction is defined by the address. Addressing is more difficult to grasp because it specifies which dimensions are being referenced (‘compute based on what?’) as well as the order in which they will be used (order matters!). ‘Compute using’ is synonymous with setting the ‘addressed’ fields in Tableau’s user interface.

When using’ specific dimensions, the ORDER of the ‘checked‘ checkboxes MATTERS. Let’s include ship mode and address by Region, followed by Ship Mode.

Next, consider the same example but change the addressing (aka direction) of the running sum to be by Ship Mode and then Region.

Setting up Visualization for Tableau Table Calculation

Here are some steps to apply Quick Visualization for Table Calculations in Tableau:

• Connect to the Sample-Superstore data source in Tableau Desktop.
• Go to a new worksheet.
• Drag the Order Date field from the Data pane’s Dimensions shelf to the Columns shelf.
• Drag the field State to the Rows shelf from the Data pane’s Dimensions.
• Drag the field Sales to Text on the Marks Card from the Data pane, under Measures.
• Drag the field Profit to Color on the Marks Card from the Data pane, under Measures.
• Select Square from the Mark Type drop-down menu on the Marks card.

This is how the Visualization update:

Steps to Create a Tableau Table Calculations

Here are the 2 Easy Steps to get started:

• Tableau Table Calculation Step 1: Creating a Visualization
• Tableau Table Calculation Step 2: Adding the Table Calculation

Tableau Table Calculation Step 1: Creating a Visualization

• Connect to the Sample-Superstore data source in Tableau.
• Go to a different worksheet.
• Drag the Order Date field from the Data pane’s Dimensions shelf to the Rows shelf. YEAR is now the dimension (Order Date).
• Right-click on the field YEAR (Order Date) on the Rows shelf and select the Field Quarter.
• Click the + icon on the field QUARTER on the Rows shelf (Order Date). The MONTH (Order Date) field is added to the shelf.
• Drag the Order Date field from the Data pane’s Dimensions shelf to the Columns shelf. The dimension’s YEAR (Order Date) field is updated once more.
• Drag the field Sales to Text on the Marks card from the Data pane, under Measures.

Tableau Table Calculation Step 2: Adding the Table Calculation

Right-click on the field SUM (Sales) on the Marks card and choose Add Table Calculation.
Do the following in the Table Calculation dialogue box:

• Choose Difference From as the Calculation Type.
• Select Table for Compute Using (across).

When you’re done, click the X in the top right corner of the Table Calculation dialogue box to close it.

Editing Table Calculations in Tableau

• Right-click on the measure in the view that has the Table Calculation applied to it and choose Edit Table Calculation.
• Make your changes in the Table Calculation dialogue box.
• When you’re done, click the X in the top right corner of the Table Calculation dialogue box to close it.

Removing Table Calculations in Tableau

Select the option Clear Table Calculation from the context menu when you right-click on the measure in the view that has the Table Calculations in Tableau applied to it. The measure’s table calculation is removed, and the Visualization is updated with the original values.

Tableau Table Calculation Functions

Table Calculations in Tableau functions enable you to compute values in a table. For example, you can compute the percentage of total sales that an individual sale represents over a year or several years.

The following are the basic Table Calculations in Tableau functions:

• LOOKUP (expression, [offset]): It returns the expression’s value in a target row that is specified as a relative offset from the current row.
• ZN ():  If the expression is not null, it returns it; otherwise, it returns zero.
• TOTAL (): It returns the total in a table calculation partition for the given expression.
• RANK (expression, ‘asc’ | ‘desc’): It returns the standard competition rank for the partition’s current row. Identical values are assigned the same rank. To specify ascending or descending order, use the optional ‘as‘ | ‘desc‘ argument. Ascending is the default.
• WINDOW AVG (expression, [beginning, end]):  It computes the average of the expressions contained within the window. Offsets from the current row are used to define the window.
• ABS (): It returns the absolute value of the number given.
• INDEX (): It returns the index of the current row in the partition, with no value sorting. The first-row index is 1.
• FIRST (): It will return the number of rows between the current row and the partition’s first row.
• LAST (): It will return the number of rows from the current row to the partition’s last row.
• CONTAINS (expression, expression to look for):  If the given string contains the specified substring, it returns true.

Conclusion

Tableau includes a number of pre-programmed calculations that you can use with the numbers in a view, such as running total, difference, percent difference, percent of the total, moving average, and more. These predefined calculations are referred to as table calculations because the results are computed using a virtual table that contains only the numbers on the view.

This blog post discussed Tableau Table Calculations. Hevo Data is a no-code data pipeline that can instantly connect multiple sources. Integrating and analyzing data from a large number of disparate sources can be difficult; this is where Hevo comes in.

Share your experience of Tableau Table Calculation in the comment section below!