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 ID Book Name Series Year Released Author
1 The Lion, the Witch and the Wardrobe The Chronicles of Narnia 1950 C.S. Lewis
2 Prince Caspian: The Return to Narnia The Chronicles of Narnia 1951 C.S. Lewis
3 The Voyage of the Dawn Treader The Chronicles of Narnia 1952 C.S. Lewis
4 The Silver Chair The Chronicles of Narnia 1953 C.S. Lewis
5 The Horse and His Boy The Chronicles of Narnia 1954 C.S. Lewis
6 The Magician's Nephew The Chronicles of Narnia 1955 C.S. Lewis
7 The Last Battle The Chronicles of Narnia 1956 C.S. Lewis
8 Daughter of the Forest Sevenwaters 1999 Juliet Marillier
9 Son of the Shadows Sevenwaters 2000 Juliet Marillier
10 Child of the Prophecy Sevenwaters 2001 Juliet Marillier
11 Heir of Sevenwaters Sevenwaters 2008 Juliet Marillier
12 Seer of Sevenwaters Sevenwaters 2010 Juliet Marillier
13 Flame of Sevenwaters Sevenwaters 2012 Juliet 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 ID Book Name Series Year Released Author Author Last Name
1 The Lion, the Witch and the Wardrobe The Chronicles of Narnia 1950 C.S. Lewis Lewis
2 Prince Caspian: The Return to Narnia The Chronicles of Narnia 1951 C.S. Lewis Lewis
3 The Voyage of the Dawn Treader The Chronicles of Narnia 1952 C.S. Lewis Lewis
4 The Silver Chair The Chronicles of Narnia 1953 C.S. Lewis Lewis
5 The Horse and His Boy The Chronicles of Narnia 1954 C.S. Lewis Lewis
6 The Magician's Nephew The Chronicles of Narnia 1955 C.S. Lewis Lewis
7 The Last Battle The Chronicles of Narnia 1956 C.S. Lewis Lewis
8 Daughter of the Forest Sevenwaters 1999 Juliet Marillier Marillier
9 Son of the Shadows Sevenwaters 2000 Juliet Marillier Marillier
10 Child of the Prophecy Sevenwaters 2001 Juliet Marillier Marillier
11 Heir of Sevenwaters Sevenwaters 2008 Juliet Marillier Marillier
12 Seer of Sevenwaters Sevenwaters 2010 Juliet Marillier Marillier
13 Flame of Sevenwaters Sevenwaters 2012 Juliet Marillier Marillier

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.

Series Number of Books in Series – at Series level of detail
The Chronicles of Narnia 7
The Chronicles of Narnia
The Chronicles of Narnia
The Chronicles of Narnia
The Chronicles of Narnia
The Chronicles of Narnia
The Chronicles of Narnia
Sevenwaters 6
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 ID Book Name Series Year Released Author Series Launched
1 The Lion, the Witch and the Wardrobe The Chronicles of Narnia 1950 C.S. Lewis 1950
2 Prince Caspian: The Return to Narnia The Chronicles of Narnia 1951 C.S. Lewis 1950
3 The Voyage of the Dawn Treader The Chronicles of Narnia 1952 C.S. Lewis 1950
4 The Silver Chair The Chronicles of Narnia 1953 C.S. Lewis 1950
5 The Horse and His Boy The Chronicles of Narnia 1954 C.S. Lewis 1950
6 The Magician's Nephew The Chronicles of Narnia 1955 C.S. Lewis 1950
7 The Last Battle The Chronicles of Narnia 1956 C.S. Lewis 1950
8 Daughter of the Forest Sevenwaters 1999 Juliet Marillier 1999
9 Son of the Shadows Sevenwaters 2000 Juliet Marillier 1999
10 Child of the Prophecy Sevenwaters 2001 Juliet Marillier 1999
11 Heir of Sevenwaters Sevenwaters 2008 Juliet Marillier 1999
12 Seer of Sevenwaters Sevenwaters 2010 Juliet Marillier 1999
13 Flame of Sevenwaters Sevenwaters 2012 Juliet Marillier 1999

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 ID Book Name Series Year Released Author  Years Since Previous Book
1 The Lion, the Witch and the Wardrobe The Chronicles of Narnia 1950 C.S. Lewis NULL  
2 Prince Caspian: The Return to Narnia The Chronicles of Narnia 1951 C.S. Lewis 1951- 1950 1
3 The Voyage of the Dawn Treader The Chronicles of Narnia 1952 C.S. Lewis 1952- 1951 1
4 The Silver Chair The Chronicles of Narnia 1953 C.S. Lewis 1953- 1952 1
5 The Horse and His Boy The Chronicles of Narnia 1954 C.S. Lewis 1954- 1953 1
6 The Magician's Nephew The Chronicles of Narnia 1955 C.S. Lewis 1955- 1954 1
7 The Last Battle The Chronicles of Narnia 1956 C.S. Lewis 1956- 1955 1
8 Daughter of the Forest Sevenwaters 1999 Juliet Marillier NULL  
9 Son of the Shadows Sevenwaters 2000 Juliet Marillier 2000- 1999 1
10 Child of the Prophecy Sevenwaters 2001 Juliet Marillier 2001- 2000 1
11 Heir of Sevenwaters Sevenwaters 2008 Juliet Marillier 2008- 2001 7
12 Seer of Sevenwaters Sevenwaters 2010 Juliet Marillier 2010- 2008 2
13 Flame of Sevenwaters Sevenwaters 2012 Juliet Marillier 2012- 2010 2

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!