top of page
Search

# Tableau Calculations: The secret to data driven success

What are Data Insights?

Data insights are the meaningful and valuable information gleaned from analyzing data, which empower decision-making, pattern recognition, and a deeper grasp of a particular subject or scenario.

Failing to effectively utilize and scrutinize organizational data undermines its capacity to fuel expansion and enhance operational effectiveness within the company.

Key Takeaways

• Comprehend Tableau calculations and the various types they encompass.

• Suggestions for mastering the creation of calculations and selecting the optimal calculation type.

• Acquire expertise in employing the best practices for crafting calculations within Tableau.

Getting Started with Tableau Calculations

Calculated Fields:

Calculated fields in Tableau refer to custom fields that are created by users using formulas or expressions to perform specific calculations on data. These calculations can involve mathematical operations, logical comparisons, string manipulations, and more.

Calculated fields enable users to discover new insights from their data, create customized metrics, and enhance visualizations with additional contextual information.

They are a powerful feature in Tableau that enables users to perform complex analysis and tailor their visualizations to meet their specific analytical needs.

Types of Calculations

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

• Basic calculations

• Level of Detail (LOD) expressions

• Table calculations - Table calculations allow you to transform values at the level of detail of the visualization only.

The type of calculation you choose depends on the needs of your analysis and the question you want to answer.

Note: The example in this article uses the Sample-Superstore data source that comes with Tableau Desktop. To follow along with the steps in this article, connect to the Sample-Superstore saved data source and navigate to Sheet 1.

Basic Calculations

Basic calculations enable the transformation of values or members either at the data source level of detail (a row-level calculation) or at the visualization level of detail (an aggregate calculation).

Row - level calculation:

In row level calculation, calculation is performed for every row in the data source. It is also called data source level calculation.

For example, consider creating a column to find the Time taken in days to ship an order.

• In Tableau, select Analysis > Create Calculated Field.

• In the Calculation Editor that opens, do the following:

• Enter a name for the calculated field. In this example, the field is called Shipping Time.

• Enter a formula. This example uses the following formula:

• Drag and drop OrderId, Order Date, Ship Date to Rows shelf. For Order Date and Ship Date pills, select the second date and change to Discrete from continuous

• Drag and drop Shipping Time to Rows. Change it to Dimension and then to Discrete.

Aggregate Calculation

In aggregate or visualization-level calculation, computations are performed based on the level of detail of  the visualization.

For example, consider showing the count of sub-categories for each category.

• Create a calculated field as shown below.

• Drag and drop Category to Rows. And drag the calculated field ‘count of sub category’ to rows. Change it to Discrete from continuous.

Level of Detail(LOD) Expression

Tableau's Level of Detail (LOD) calculations are a powerful feature that allows users to perform calculations at different levels of granularity within their data.

LOD calculations provide users with flexibility in defining the level of granularity at which calculations are performed. This enables users to analyze data at various levels of detail, such as overall, by category, or by specific dimensions.

LOD calculations are useful in a variety of scenarios, such as:

• Calculating aggregates at a different level of detail than the visualization.

• Comparing values against different levels of granularity.

• Performing complex analyses that require aggregating data across multiple dimensions.

Types of Level of Detail(LOD) Expression

LOD expressions enable computation of values at both the data source and visualization levels. It allows the aggregation of data at specified dimension level.

Moreover, they offer greater control over the granularity of computation.They can be executed at

• a more detailed level (INCLUDE),

• a less detailed level (EXCLUDE),

• a completely independent level (FIXED).

LOD expression syntax

A level of detail expression has the following structure:

{ [FIXED | INCLUDE | EXCLUDE] <dimension declaration> : <aggregate expression>}

{ }

The entire level of detail expression is enclosed in curly braces.

Table Calculations

Table calculations in Tableau are dynamic calculations that are performed on the results of a visualization or a subset of data within a visualization. They allow users to perform complex calculations, comparisons, and transformations directly within Tableau's visualizations, enhancing the depth and flexibility of analysis.

A table calculation is a transformation you apply to the values in a visualization. Table calculations are a special type of calculated field that computes the local data in Tableau. They are calculated based on what is currently in the visualization and do not consider any measures or dimensions that are filtered out of the visualization.

Types of Table Calculations

• Difference From calculation

• Percent Difference From calculation

• Percent From calculation

• Percent of Total calculation

• Percentile calculation

• Rank calculation

• Running Total calculation

• Moving calculation

Best Practices for Creating Calculations in Tableau

General Rule: Avoid using a calculated field multiple times in another calculation

Referencing the same calculated field multiple times within another calculation will result in performance issues. If you use a calculated field within a calculation (also known as creating a Nested Calculation), try to reference it only once in the calculation

Tip 1: Convert multiple equality comparisons to a CASE expression or a group.

Consider following example, which uses multiple logical OR operator. Instead we can use CASE-WHEN to simplify our solution.

Solution:

Tip 2: Convert multiple string calculations into a single REGEXP expression

In the below example, it uses IF condition multiple times to check for a sub-category. Instead, it is best practice to use single REGEXP expression.

Solution:

Tip 3: Manipulate strings with REGEXP instead of LEFT, MID, RIGHT, FIND, LEN

Similarly instead of using LEFT, RIGHT, MID functions multiple times, we can use single REGEXP expression.

Solution :

Tip 4: Do not use sets in calculations

If you are using sets in a calculation, consider replacing them with an alternative, but equivalent calculation. If the set is simple, you can create a calculated field that returns the same result as the set. If the set is more complex, consider creating a group that maps all the elements in the set to a given value or attribute, such as 'IN', and then modify the calculation to check for that value/attribute.

Tip 5: Do not use sets to group your data

Sets are meant to make comparisons on subsets of data. Groups are meant to combine related members in a field. Converting sets to groups is not recommended.

How to choose the Right Calculation Type?

Choosing the type of calculation to use for your analysis is not always easy. When trying to decide, consider the following questions

Basic expression or table calculation?

Question 1:

Do you already have all the data values you need on the visualization?

• If the answer is yes: You can use a table calculation.

• If the answer is no: Use a basic calculation.

Basic expression or Level of Detail (LOD) expression?

If you don't have all the data you need on the visualization, you need your calculation to be passed through to the data source. This means you must use a basic calculation or an LOD Expression.

Question 2:

Does the granularity of your question match either the granularity of the visualization or the granularity of the data source?

• If the answer is yes: Use a basic expression.

• If the answer is no: Use a Level of Detail (LOD) expression.

Table calculation or Level of Detail (LOD) expression?

When choosing between a table calculation or an LOD calculation, the process is very similar to choosing between a table calculation and a basic expression. Ask yourself the following questions:

Do you already have all the data values you need on the visualization?

• If the answer is Yes, then use a table calculation.

• If the answer is No, then ask yourself: Does the granularity of the question match either the granularity of the visualization or the granularity of the data source? If the answer is No, then use an LOD calculation.

Table calculations only

There are some scenarios where only a table calculation will do. These include:

• Ranking

• Recursion (e.g. cumulative totals)

• Moving calculations (e.g. rolling averages)

• Inter-row calculations (e.g. period vs. period calculations)

References: