Types of Calculations in Tableau

This article explains the types of calculations you can use in Tableau. You'll learn the difference between each calculation and how they are computed.

There are three main types of calculations you can use to create calculated fields in Tableau:

Basic expressions

Basic expressions allow you to transform values or members at the data source level of detail (a row-level calculation) or at the visualisation level of detail (an aggregate calculation).

For example, consider the following sample table, which contains data on two fantasy authors and their books. Perhaps you want to create a column with only the author's last name and a column that displays how many books are in each series.

Book IDBook NameSeriesYear ReleasedAuthor
1The Lion, the Witch and the WardrobeThe Chronicles of Narnia1950C.S. Lewis
2Prince Caspian: The Return to NarniaThe Chronicles of Narnia1951C.S. Lewis
3The Voyage of the Dawn TreaderThe Chronicles of Narnia1952C.S. Lewis
4The Silver ChairThe Chronicles of Narnia1953C.S. Lewis
5The Horse and His BoyThe Chronicles of Narnia1954C.S. Lewis
6The Magician's NephewThe Chronicles of Narnia1955C.S. Lewis
7The Last BattleThe Chronicles of Narnia1956C.S. Lewis
8Daughter of the ForestSevenwaters1999Juliet Marillier
9Son of the ShadowsSevenwaters2000Juliet Marillier
10Child of the ProphecySevenwaters2001Juliet Marillier
11Heir of SevenwatersSevenwaters2008Juliet Marillier
12Seer of SevenwatersSevenwaters2010Juliet Marillier
13Flame of SevenwatersSevenwaters2012Juliet Marillier

Row-level calculations

To create a column that displays the author's last name for every row in the data source, you can use the following row-level calculation that splits on a space:

SPLIT([Author], '', 2 )

The result can be seen below. The new column, titled Author Last Name is shown on the far right. The colours demonstrate the level of detail the calculation is performed at. In this case, the calculation is performed at the row-level of the data source, so each row is coloured separately.

Book IDBook NameSeriesYear ReleasedAuthorAuthor Last Name
1The Lion, the Witch and the WardrobeThe Chronicles of Narnia1950C.S. LewisLewis
2Prince Caspian: The Return to NarniaThe Chronicles of Narnia1951C.S. LewisLewis
3The Voyage of the Dawn TreaderThe Chronicles of Narnia1952C.S. LewisLewis
4The Silver ChairThe Chronicles of Narnia1953C.S. LewisLewis
5The Horse and His BoyThe Chronicles of Narnia1954C.S. LewisLewis
6The Magician's NephewThe Chronicles of Narnia1955C.S. LewisLewis
7The Last BattleThe Chronicles of Narnia1956C.S. LewisLewis
8Daughter of the ForestSevenwaters1999Juliet MarillierMarillier
9Son of the ShadowsSevenwaters2000Juliet MarillierMarillier
10Child of the ProphecySevenwaters2001Juliet MarillierMarillier
11Heir of SevenwatersSevenwaters2008Juliet MarillierMarillier
12Seer of SevenwatersSevenwaters2010Juliet MarillierMarillier
13Flame of SevenwatersSevenwaters2012Juliet MarillierMarillier

Aggregate calculations

To create a column that displays how many books are in each series, you can use the following aggregate calculation:

COUNT([Series])

The result can be seen below. The new column, titled Number of Books in Series – at Series level of detail shows how that calculation would be performed at the Series level of detail in the view. The colours help demonstrate the level of detail in which the calculation is being performed.

SeriesNumber of Books in Series – at Series level of detail
The Chronicles of Narnia7
The Chronicles of Narnia
The Chronicles of Narnia
The Chronicles of Narnia
The Chronicles of Narnia
The Chronicles of Narnia
The Chronicles of Narnia
Sevenwaters6
Sevenwaters
Sevenwaters
Sevenwaters
Sevenwaters
Sevenwaters

In Tableau, the data looks like this:

But if you drag in Book Id, (which is a more granular field), the calculation updates based on that new granularity since aggregate calculations are performed at the visualisation level of detail.

Level of Detail (LOD) expressions

Just like basic expressions, LOD expressions allow you to compute values at the data source level and the visualisation level. However, LOD expressions give you even more control on the level of granularity you want to compute. They can be performed at a more granular level (INCLUDE), a less granular level (EXCLUDE), or an entirely independent level (FIXED).

For more information, see Create Level of Detail Expressions in Tableau(Link opens in a new window).

For example, consider the same sample table as above. If you wanted to compute when a book series was launched, you might use the following LOD expression:

{ FIXED [Series]:(MIN([Year Released]))}

The result can be seen below. The new column, titled Series Launched, displays the minimum year for each series. The colours help demonstrate the level of detail in which the calculation is being applied.

Book IDBook NameSeriesYear ReleasedAuthorSeries Launched
1The Lion, the Witch and the WardrobeThe Chronicles of Narnia1950C.S. Lewis1950
2Prince Caspian: The Return to NarniaThe Chronicles of Narnia1951C.S. Lewis1950
3The Voyage of the Dawn TreaderThe Chronicles of Narnia1952C.S. Lewis1950
4The Silver ChairThe Chronicles of Narnia1953C.S. Lewis1950
5The Horse and His BoyThe Chronicles of Narnia1954C.S. Lewis1950
6The Magician's NephewThe Chronicles of Narnia1955C.S. Lewis1950
7The Last BattleThe Chronicles of Narnia1956C.S. Lewis1950
8Daughter of the ForestSevenwaters1999Juliet Marillier1999
9Son of the ShadowsSevenwaters2000Juliet Marillier1999
10Child of the ProphecySevenwaters2001Juliet Marillier1999
11Heir of SevenwatersSevenwaters2008Juliet Marillier1999
12Seer of SevenwatersSevenwaters2010Juliet Marillier1999
13Flame of SevenwatersSevenwaters2012Juliet Marillier1999

In Tableau, the calculation remains at the Series level of detail since it uses the FIXED function.

Viz showing the date 1950 for The Chronicles of Narnia and 1999 for Sevenwaters

If you add another field to the view (which adds more granularity) the values for the calculation are not affected, unlike an aggregate calculation.

Viz showing the date 1950 repeated for all Narnia books and 1999 for all Sevenwaters books

Table calculations

Table calculations allow you to transform values at the level of detail of the visualisation only.

For more information, see Transform Values with Table Calculations(Link opens in a new window).

For example, consider the same sample table as above. If you wanted to compute the number of years since the author released their last book, you might use the following table calculation:

ATTR([Year Released]) - LOOKUP(ATTR([Year Released]), -1)

The result is shown below. The new column, titled Years Since Previous Book, displays the number of years between the book released in that row and the book released in the previous row (on the far right-side of the column) and demonstrates how the table calculation is being computed (on the left-side of the column).

The colours help demonstrate how the table calculation is being computed. In this case, the table calculation is being computed down each pane.

Note: Depending on the table calculation and how it is being computed across the table, the results may vary. For more information, see Transform Values with Table Calculations(Link opens in a new window).

Book IDBook NameSeriesYear ReleasedAuthor Years Since Previous Book
1The Lion, the Witch and the WardrobeThe Chronicles of Narnia1950C.S. LewisNULL 
2Prince Caspian: The Return to NarniaThe Chronicles of Narnia1951C.S. Lewis1951-19501
3The Voyage of the Dawn TreaderThe Chronicles of Narnia1952C.S. Lewis1952-19511
4The Silver ChairThe Chronicles of Narnia1953C.S. Lewis1953-19521
5The Horse and His BoyThe Chronicles of Narnia1954C.S. Lewis1954-19531
6The Magician's NephewThe Chronicles of Narnia1955C.S. Lewis1955-19541
7The Last BattleThe Chronicles of Narnia1956C.S. Lewis1956-19551
8Daughter of the ForestSevenwaters1999Juliet MarillierNULL 
9Son of the ShadowsSevenwaters2000Juliet Marillier2000-19991
10Child of the ProphecySevenwaters2001Juliet Marillier2001-20001
11Heir of SevenwatersSevenwaters2008Juliet Marillier2008-20017
12Seer of SevenwatersSevenwaters2010Juliet Marillier2010-20082
13Flame of SevenwatersSevenwaters2012Juliet Marillier2012-20102

In Tableau, the data looks like this:

Viz showing the correct years since previous book for each book

However, if you change the visualisation in a way that affects the layout, such as removing a dimension from the view, the calculation values change.

For example, in the image below, Author is removed from the viz. Since the table calculation is computed by pane, removing Author changes the granularity and layout of the viz (instead of two panes there is now only one). The table calculation therefore calculates the time between 1956 and 1999.

Viz showing incorrect years since previous book for the Sevenwaters books


Continue to Choosing the Right Calculation Type

See Also

Understanding Calculations in Tableau(Link opens in a new window)

Tips for Learning How to Create Calculations(Link opens in a new window)


Thanks for your feedback!Your feedback has been successfully submitted. Thank you!