Dynamic measure swap in Tableau - Cell-by-Cell Formula Trace

Sample Data

This data shows products with their Sales and Profit values. Cell D2 holds the user's choice of measure to display dynamically.

Cell	Value
A1	Product
B1	Sales
C1	Profit
A2	Apples
B2	1000
C2	200
A3	Bananas
B3	1500
C3	300
A4	Cherries
B4	1200
C4	250
D1	Selected Measure
D2	Sales

Formula Trace

IF [Selected Measure] = 'Sales' THEN SUM([Sales]) ELSE SUM([Profit]) END

Step 1: [Selected Measure]

Step 2: [Selected Measure] = 'Sales'

Step 3: SUM([Sales])

Step 4: IF TRUE THEN 3700 ELSE SUM([Profit]) END

Cell Reference Map

     A       B       C       D
1 |Product| Sales | Profit|Selected Measure|
2 |Apples | 1000  |  200  |    Sales     |
3 |Bananas| 1500  |  300  |              |
4 |Cherries|1200  |  250  |              |

The formula uses the value in cell D2 to decide which measure to sum: Sales (column B) or Profit (column C).

Result

     A       B       C       D       E
1 |Product| Sales | Profit|Selected Measure|Result|
2 |Apples | 1000  |  200  |    Sales     | 3700 |
3 |Bananas| 1500  |  300  |              |      |
4 |Cherries|1200  |  250  |              |      |

The result cell E2 shows 3700, the sum of Sales, because the selected measure is 'Sales'.

Sheet Trace Quiz - 3 Questions

What does the formula return if the Selected Measure is 'Profit'?

A Sum of Sales

B Sum of Profit

C Zero

D Error

Key Result

IF [Selected Measure] = 'Sales' THEN SUM([Sales]) ELSE SUM([Profit]) END