Compute a Table Calculation Across Multiple Measures

Published: 03 Dec 2013
Last Modified Date: 15 Apr 2019


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.


Tableau Desktop


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

Step 2 - Create the Percent Difference Calculation
  1. Select Analysis > Create Calculated Field
  2. 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
  3. Right-click [% Diff Stage 1 to 2] in the data pane, and select Default Properties > Number Format...
  4. In the Default Number Format dialog, select Percentage and click OK
  5. Drag [% Diff Stage 1 to 2] to the Measure Values card
  6. 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.
Did this article resolve the issue?