Tableau Desktop and Web Authoring Help

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
• Level of Detail (LOD) expressions
• Table calculations

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.

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.

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.

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.

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

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

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).

In Tableau, the data looks like this:

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.

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)