KNOWLEDGE BASE

Compute a Table Calculation Across Multiple Measures


Published: 03 Dec 2013
Last Modified Date: 05 Jun 2018

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 to that manually specifies the measures to be used in the table function

The attached example workbook uses the sample data set Superstore to demonstrate the following directions:

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 1]))
      - ZN(SUM([Stage 2])))
       / ABS(ZN(SUM([Stage 2])))

  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?