Tableau Swapping Measures Using Parameters

You may want to set up a view that can be modified to show different measures. You can set up this view by using a parameter control and a calculated field. The calculated field replaces the measure on the view and changes as the parameter control is changed.

Create the parameters

Create a parameter where the values are the names of the measures you are going to select. For this example, create two identical parameters, named Placeholder 1 Selector and Placeholder 2 Selector.

Step 1

Connect to your data source. This example uses the Sample - Superstore data source.

Step 2

From the Data window drop-down menu, select Create Parameter.

Step 3

In the Create Parameter dialog box, do the following tasks:

1. Type a Name for the parameter. For this example, type Placeholder 1 Selector.
2. In the Data type drop-down list, select String.
3. For Allowable values, select List.
4. In the List of values area, type the names of the measures you want available for the analysis.
5. When finished, click OK.

Step 4

Repeat steps 2-3 to create a second parameter and name it Placeholder Selector 2.

Option 1: Create calculated fields that change measures

There are two options for the calculation. You can either change measures or you can change aggregations. For either option, you create two calculated fields. For this example, these two calculated fields are named Placeholder 1 and Placeholder 2. Follow the steps below to create a calculated field that swaps measures in the analysis.

Step 1

Select Analysis > Create Calculated Field.

Step 2

In the Calculated Field dialog box, write a formula that follows the structure shown below:

CASE [Parameter]
 WHEN "Measure 1 Name" THEN [Measure 1]
 WHEN "Measure 2 Name" THEN [Measure 2]
 WHEN "Measure N Name" THEN [Measure N]
END

For this example, the formula is:

CASE [Placeholder 1 Selector]
 WHEN "Discount" THEN [Discount]
 WHEN "Order Quantity" THEN [Order Quantity]
 WHEN "Profit" THEN [Profit]
 WHEN "Sales" THEN [Sales]
 WHEN "Unit Price" THEN [Unit Price]
END

Step 3

When finished, click OK.

Step 4

Create another calculated field just like the first one, but this time name it Placeholder 2 and use the Placeholder 2 Selector parameter. The formula for the second calculated field is below:

CASE [Placeholder 2 Selector]
 WHEN "Discount" THEN [Discount]
 WHEN "Order Quantity" THEN [Order Quantity]
 WHEN "Profit" THEN [Profit]
 WHEN "Sales" THEN [Sales]
 WHEN "Unit Price" THEN [Unit Price]
END

When you use these calculated fields in the view, the parameter control will let you switch out which measure is used.

Option 2: Create calculated fields that change aggregations

In the first option, all of the measures use the same aggregation. Follow the steps below to create a calculated field that uses measures that have different aggregations.

Step 1

Select Analysis > Create Calculated Field.

Step 2

In the Calculated Field dialog box, type a formula using the basic structure shown below:

CASE [Parameter]
  WHEN "Measure 1 Name" THEN SUM([Measure 1])
  WHEN "Measure 2 Name" THEN AVG([Measure 2])
  WHEN "Measure N Name" THEN COUNT([Measure N])
END

For this example, the formula is:

CASE [Placeholder 1 Selector]
  WHEN "Discount" THEN AVG([Discount] )
  WHEN "Order Quantity" THEN SUM([Order Quantity])
  WHEN "Profit" THEN SUM([Profit])
  WHEN "Sales" THEN AVG([Sales])
  WHEN "Unit Price" THEN AVG([Unit Price])
END

Step 3

When finished, click OK.

Step 4

Create another calculated field just like the first one, but this time name it Placeholder 2 and use the Placeholder 2 Selector parameter. The formula for the second calculated field is below:

CASE [Placeholder 2 Selector]
  WHEN "Discount" THEN AVG([Discount] )
  WHEN "Order Quantity" THEN SUM([Order Quantity])
  WHEN "Profit" THEN SUM([Profit])
  WHEN "Sales" THEN AVG([Sales])
  WHEN "Unit Price" THEN AVG([Unit Price])
END

Note: All measures must either be aggregated or disaggregate. You cannot mix aggregated and disaggregated measures. For example, you couldn't set up the calculation with AVG(Discount) and Sales. Both measures must be aggregated (e.g., AVG(Discount) and SUM(Sales)) or both be disaggregated (e.g., Discount and Sales).

Set up the view

Step 1

Drag the Placeholder 2 field to the Columns shelf and the Placeholder 1 field to the Rows shelf.

Step 2

Drag Customer Name to the Detail.

Step 3

In the Parameters area of the Data window, right-click each parameter and select Show Parameter Control.

The parameter controls let you select the measures to be used on the X and Y axis of the view. For example, below, the view on the left shows profit vs. sales while the one on the right shows Order Quantity vs. Discount.