Tableau Table Calculations

A table calculation is a transformation applied to the values in a visualization. Tableau Calculations are a special type of calculated field that calculates the local data in Tableau. 

Table calculations can be used for a variety of purposes, including:

• Transforming values to rankings
• Transforming values to show running totals
• Transforming values to show the percent of total

For any Tableau visualization, a virtual table is determined by the dimensions in the view. This table is not similar to the tables in your data source. Usually, the virtual table is determined by the dimensions within the detail level.

When we add a table calculation, we must use all dimensions in the level of detail either for partitioning or for addressing.

The dimensions that defines how to group the calculation (the scope of data it is performed) are partitioning fields. The table calculations are performed separately within each partition.

The remaining dimensions, on which the table calculation is performed, are called addressing fields, and determine the direction of the calculation.

Table (across): This computes across the length of the table and restarts after every partition.

For example, in the below table, the calculation is computed across columns (YEAR (Order Date)) for every row (MONTH (Order Date)).

Table (down): This computes down the length of the table and restarts after every partition.

For example, in the below table, the calculation is computed down rows (MONTH (Order Date)) for every column (YEAR (Order Date)).

Table (Across then down): This computes across the length of the table, and then down the length of the table.

For example, in the below table, the calculation is computed across the columns (YEAR (Order Date)), down the row (MONTH (Order Date)), and then again across columns for the entire table.

Table (down then across): This computes down the length of the table, and then across the length of the table.

For example, in the following table, the calculation is computed down the rows (MONTH (Order Date)), across the column (YEAR (Order Date), and then down rows again.

Pane (down): This computes down an entire pane.

For example, in the below table, the calculation is computed down rows (MONTH (Order Date)) for a single pane.

Pane (across then down): This computes across an entire pane and then down the pane.

For example, in the below table, the calculation is computed across the columns (YEAR (Order Date)) for the length of the pane, down the row (MONTH (Order Date)), and then across columns for the length of the pane again.

Pane (down then across): This computes down an entire pane and then across the pane.

For example, in the below table, the calculation is computed down the rows (MONTH (Order Date)) for the length of the pane, across the column (YEAR (Order Date)), and then down the length of the pane again.

Cell: It computes within a single cell.

Specific Dimensions: It computes only within the dimensions you specify.

For example, in the below visualization, the dimensions, Month of Order Date and Quarter of Order Date, all are the addressing fields (since they are selected), and the field Year of Order Date is the partitioning field (because it is not selected). Therefore, the calculation transforms the difference from each month across all quarters within a year. The calculation starts over every year.

Create a table calculation

Step 1: Build the visualization.

1. Open the Tableau and connect to the Sample-Superstore data source.
2. Navigate to a new worksheet.
3. From the Data pane, under Dimensions, drag the field Order Date to the Rows shelf. The dimension updates to YEAR (Order Date).
4. On the Rows shelf, right-click on the field YEAR (Order Date) and select the Field Quarter.
5. On the Rows shelf, click on the + icon on the field QUARTER (Order Date). The field MONTH (Order Date) is added to the shelf.
6. From the Data pane, under Dimensions, drag the field Order Date to the Columns shelf. The dimension updates to the field YEAR (Order Date) again.
7. From the Data pane, under Measures, drag the field Sales to Text on the Marks card.

The updates will look like this:

Step 2: Add the table calculation

1. On the Marks card, right-click on the field SUM (Sales) and select the option Add Table Calculation.
2. In the Table Calculation dialog box, do the following:

• For Calculation Type: select Difference From.
• For Compute Using, select Table (across).
• When it is finished, click on the X in the top corner of the Table Calculation dialog box to exit it.

Edit a table calculation:

1. Right-click on the measure in the view with the table calculation applied to it and select the option Edit Table Calculation.
2. In the Table Calculation dialog box, make your changes.
3. When it is finished, click on the X in the top corner of the Table Calculation dialog box to exit it.

Remove a table calculation:

Right-click on the measure in the view with the table calculation applied to it and select the option Clear Table Calculation. The table calculation is removed from the measure and the visualization updates with the original values.

Quick Table Calculations:

Quick table calculations allow you to apply a common table calculation quickly to your visualization.

The following quick table calculations options are available in Tableau:

• Running total
• Difference
• Percent difference
• Percent of total
• Rank
• Percentile
• Moving average
• YTD total
• Compound growth rate
• Year of year growth
• YTD growth

Steps to apply a quick table calculation to the visualization

Set up the visualization

1. Open the Tableau Desktop and connect to the Sample-Superstore data source.
2. Navigate to a new worksheet.
3. From the Data pane, under Dimensions, drag the field Order Date to the Columns shelf.
4. From the Data pane, under Dimensions, drag the field State to the Rows shelf.
5. From the Data pane, under Measures, drag the field Sales to Text on the Marks Card.
6. From the Data pane, under Measures, drag the field Profit to Color on the Marks Card.
7. On the Marks card, click on the Mark Type drop-down and select the option Square.

The visualization updates will look like this:

Apply the quick table calculation: On the Marks card, right-click on SUM (Profit) and select Quick Table Calculation > Moving Average.

A delta symbol appears on the field, which indicates that a quick table calculation is applied to the field. The colors in the visualization update to show the moving average of profit across the years.

Table Calculation Functions

Table calculation functions allow you to perform computations on values in a table.

For example, you can calculate the percent of total an individual sale is for the year or several years.

The basic table calculation functions are:

• LOOKUP (expression, [offset]): It returns the value of the expression in a target row, specified as a relative offset from the current row.
• ZN (): It returns the expression if it is not null; otherwise, it returns zero.
• TOTAL (): It returns the total for the given expression in a table calculation partition.
• RANK (expression, [‘asc’|’desc’]): It returns the standard competition rank for the current row in the partition. Identical values are assigned an identical rank. Use the optional ‘asc’ | ‘desc’ argument to specify ascending or descending order. The default is descending.
• WINDOW_AVG (expression, [start, end]): It returns the average of the expression within the window. The window is defined using offsets from the current row.
• ABS (): It returns the absolute value of the given number.
• INDEX (): It returns the index of the current row in the partition, without any sorting with regard to value. The first-row index starts at 1
• FIRST (): It will return the number of rows from the current row to the first row in the partition.
• LAST (): It will return the number of rows from the current row to the last row in the partition.
• CONTAINS (expression, expression to search for): It returns true if the given string contains the specified substring.