KNOWLEDGE BASE

## Calculating Difference Between Two Values of the Same Measure in a View

Published: 17 Jun 2015

### Issue

How to create a calculation to find the difference between two values of the same measure in a view.

For example, if the original view shows sales for 4 years, then the final view would show one column with the difference between sales for two specific years.

Tableau Desktop

### Resolution

The instructions for both options can be reviewed in the attached workbook.

### Option 1

The below example compares sales between two years (e.g. 2015 sales - 2013 sales). Adjust the number at the end of the `LOOKUP` calculation to increase the difference in computation.
1. In Tableau Desktop, right-click the sheet name and select Duplicate sheet. This will retain the view structure (such as YEAR on Columns and Measures on Text, etc).
2. Navigate to Analysis > Create calculated field...
3. Name the calculated field and enter a formula similar to the following:
`SUM([Sales]) - LOOKUP(ZN(SUM([Sales])), -2)`
Note: -2 is calculated as “two cells previous”. If the data were viewed in a table, this means that the calculation will subtract the number two columns previous from the current column. If the view is changed from YEAR to QUARTER, this will change the result from 2 years previous to 2 quarters previous.
1. Drag the new calculation to the view, replacing the original measure.

### To isolate one desired value for the calculation:

1.  Create a calculated field with a name like "Last Filter" with a calculation similar to the following:
`LAST() = 0`
2. Drag [Last Filter] to the Filters shelf
3. In the Filter dialog, check True and click OK
4. Right-click on Year, clear the Show Header selection.

### Option 2

1. Use the method in Isolating Dimension Members to Use in Calculations and Filters to create separate calculated for sales for each year
2. Create a calculated field with a name like "Sales Difference (option 2)" with a calculation similar to the following:
`SUM([2015 Sales]) - SUM([2013 Sales])`
3. Drag [Sales Difference (option 2)] to Text on the Marks card

### Cause

For option 1, the quick table calculation Difference will show a difference in every column. A calculation to find the difference will also appear in every column, therefore a new view needs to be built to isolate the desired year.