Compute a Table Calculation Across Multiple Measures

Published: 03 Dec 2013
Last Modified Date: 20 Jul 2023

Question

How to compute a table calculation, such as percent difference, across multiple measures rather than across dimension members. For example calculating the percent difference the number of members in each stage.

Environment

Tableau Desktop

Answer

Option 1: Pivot the measures

For some data sources it is possible to pivot data in Tableau Desktop. For directions, please see Pivot Data from Columns to Rows For other data sources, it will be necessary to pivot the data in the database.

After the data is pivoted, add a table calculation to [Pivot Field Values]. For directions, please see Transform Values with Table Calculations

Option 2: Create a calculated field that manually specifies the measures to be used in the table function

Step 1 - Build the Original View

Drag [Project ID] to the Rows shelf
Right-click and drag [Date] to the Columns shelf
In the Drop Field dialog, select MY(Date) and click OK
Drag [Stage 1] to Text on the Marks card
Double-click [Stage 2] and [Stage 3] in the data pane to add them to the view

Step 2 - Create the Percent Difference Calculation

Select Analysis > Create Calculated Field
In the Calculated Field dialog box, do the following, and then click OK:
- Name the calculated field. In this example, the calculated field is named "% Diff Stage 1 to 2"
- In the formula field, create a calculation similar to the following:
  (ZN(SUM([Stage 2])) - ZN(SUM([Stage 1]))) / ABS(ZN(SUM([Stage 1])))
- For help with writing the formula to use, start by converting the quick table calculation into a calculated field, see Converting a Quick Table Calculation into a Calculated Field
Right-click [% Diff Stage 1 to 2] in the data pane, and select Default Properties > Number Format...
In the Default Number Format dialog, select Percentage and click OK
Drag [% Diff Stage 1 to 2] to the Measure Values card
Repeat steps 1-6 for [% Diff Stage 2 to 3]

Note: This method will create additional rows rather than an additional column. See Add Additional Columns to A Crosstab when Every Row is a Measure for directions on how to reformat the view.