KNOWLEDGE BASE

Calculating Difference Between Two Measure Values in a View


Published: 17 Jun 2015
Last Modified Date: 27 Feb 2017

Issue

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

Environment

Tableau Desktop

Resolution

The following instructions can be reviewed in the attached workbook. The below example compares every two years (2014 vs 2012, 2013 vs 2011). 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. Right-click every year except the desired year and select Hide. This hides the data without impacting the results of the table calculation – quick filters will limit the data available to the table calculation.
  2. Right-click on Year, clear the Show Header selection.

 

Cause

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.
Did this article resolve the issue?