Tableau calculated field difference between two columns

Tableau FAQs
December 13, 2018

Table of Contents Show

Environment
To isolate one desired value for the calculation:
Additional Information
How do you find the difference between two columns in Tableau?
How do you find the difference between two values in Tableau?
What does the ZN () calculation do in Tableau?
How do I merge two columns in a Tableau using calculated fields?

I have two columns that I am trying to find the difference from rather than the total. Can someone help me set up the formula?

To compare data from two fields and calculate the difference, you can use the LOOKUP function. Assuming you have data in fields for 2020 and 2019, then you could use the following expression to find the difference:

LOOKUP(

    SUM([OrderQty]),

    LAST()) -

LOOKUP(SUM([OrderQty]),

    FIRST())

LOOKUP( – Returns the value of the expression in a target row, specified as a relative offset from the current row. Use FIRST() + n and LAST() – n as part of your offset definition for a target relative to the first/last rows in the partition. If offset is omitted, the row to compare to can be set on the field menu. This function returns NULL if the target row cannot be determined.

SUM([OrderQty]), – here we specify data aggregation

LAST()) – here we specify the offset

Up Next:

Read How to create a triple dual axis on a Tableau worksheet chart?

Published: 17 Jun 2015
Last Modified Date: 11 Oct 2022

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.

Environment

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.

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).
Navigate to Analysis > Create calculated field...
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.

Drag the new calculation to the view, replacing the original measure.

To isolate one desired value for the calculation:

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

Option 2

Use the method in Isolating Dimension Members to Use in Calculations and Filters to create separate calculated for sales for each year
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])
```
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.

Additional Information

Discuss this article...

How do you find the difference between two columns in Tableau?

How do I find the difference between the two columns in Tableau?.

LOOKUP( – Returns the value of the expression in a target row, specified as a relative offset from the current row. ... .

SUM([OrderQty]), – here we specify data aggregation..

LAST()) – here we specify the offset..

How do you find the difference between two values in Tableau?

Right-click a measure in the view and select Add Table Calculation. Calculates the difference between the current value and the previous value in the partition. This is the default value.

What does the ZN () calculation do in Tableau?

The ZN function when used in a calculation returns the default values if it is not nulls, otherwise it returns zeros. In summary, we can say that we use the ZN function to replace null values with zeros within our computations.

How do I merge two columns in a Tableau using calculated fields?

To combine the fields, select multiple dimensions in the Data pane and then right-click (control-click on a Mac) the fields and select Create > Combined Field.