Tableau Calculations

Calculations in Tableau

Tableau comes with predefined calculations. Calculations are nothing but applying transformations on values. They are useful in views for creating a total, difference, average, percent difference, percent of the total, etc. It allows users to create a calculated field based on the data source. When your dataset does not provide the information that you need, you can create calculated fields by applying calculations. The calculated field can be used to create visualizations. You can use calculations when you want to segment data, filter through results, converting a data type of a field, etc.

Types of calculations

Users can create calculated fields in Tableau using three main types of calculations.

Basic expressions

It is useful to transform values at the data source level of detail, i.e., row-level calculation or at the visualization level of detail, i.e., aggregate calculation. The basic calculations scale very well. These are mostly used on the underlying data source.

Level of Detail (LOD) expressions

The LOD expressions are also useful to transform values at the data source level and the visualization level. The LOD expressions are dependent on database performance since they are expressed as nested SELECT. Unlike basic expressions, the LOD expressions provide more control on the level of granularity. The granularity levels can be,
A more granular level (INCLUDE)
A less granular level (EXCLUDE)
An independent level (FIXED)

Table calculations

The table calculations only allow data transformations at the level of detail of the visualization. These calculations are performed on the query result set. Users can apply this calculation only on the virtual table that has numbers of the view. Table calculations are useful for rankings, running totals, and showing the percent of the total. If you want to reduce the performance issue, aggregate the data first, and then perform the calculation on it.

Ad-hoc calculations

Tableau also provides Ad-hoc calculations that can be used to create and update as you work on a field in a view. They are also known as type-in or in-line calculations. They are supported on Rows, Columns, Marks, and Measure Values shelves. If there are any errors in ad-hoc calculations, they are underlined in red. When you are editing an ad-hoc calculation, you can only see the current line. These calculations won't be named by default but will be saved when you close the workbook. When you save an ad-hoc calculation, it will work as same as the calculations that are created in the calculation editor.

Building blocks of calculation

The calculations in Tableau have four basic components or building blocks.

Functions - Pre-defined statements are used to transform values or members in a field.
Fields - Columns or dimensions of a data source.
Operators - Symbols that represent an operation.
Literal expressions - Constant values that can be represented as is.

Calculations might also contain,
Parameters - Variables that can be used in calculations and later replaced with constant values.
Comments - A text about a calculation or a part of the calculation, which should not be included in the calculation.

Advantages of calculations

• Here are the key advantages of using calculations in Tableau.
• Users can create advanced calculations even without having to know the underlying syntax.
• The table calculations can be saved as calculated fields for future use.
• The calculations are performed very faster.
• Users can also learn syntax and functions while working with table calculations.
• Tableau provides spotlighting through which users can show discrete thresholds based on the values of a measure. The spotlighted data can be shown in color codes for easy visibility.
• You can directly drag and drop measures into the calculation editor.

Tableau Certification Training

• Master Your Craft
• Lifetime LMS & Faculty Access
• 24/7 online expert support
• Real-world & Project Based Learning

Explore Curriculum

Tableau calculation functions

Tableau offers many functions that can be used in calculations. Here are some of the table calculation functions that are available.

• FIRST() - It returns all the rows from the current row to the first row in the partition.
• INDEX( ) - It returns the current row index of the partition.
• LAST( ) - It returns all the rows from the current row to the last row in the partition.
• LOOKUP(expression, [offset]) - It returns the target field's values of the expression, which is specified as a relative offset.
• PREVIOUS_VALUE(expression) - It returns the value of the calculation in the previous row.
• RANK(expression, ['asc' | 'desc']) - It returns the rank of the current row. Similar values will get identical rank. The 'asc' and 'desc' argument specify the order of ranking.
• RANK_DENSE(expression, ['asc' | 'desc']) - It returns the dense rank of the current row.
• RANK_MODIFIED(expression, ['asc' | 'desc']) - It returns the modified rank of the current row.
• RANK_PERCENTILE(expression, ['asc' | 'desc']) - It returns the percentile rank of the current row.
• RUNNING_AVG(expression) - It returns the running average of the given expression.
• RUNNING_COUNT(expression) - It returns the running count of the given expression.

If you want to Explore more about Tableau? then read our updated article - Tableau Tutorial

Creating a calculated field

Once you choose a calculation, you have to create a calculated field. Open Tableau, select 'Analysis', and click on 'Create Calculated Field'. You will get a calculation editor where you can create your calculation. Give a name for your calculation, enter the formula, and click on 'OK'. You can observe the newly calculated field being added to the 'Measures' section in the Data pane. The calculated field stands out from the rest as it contains a '=' sign next to the data type icon. This calculated field can then be used in creating visualizations.

Subscribe to our youtube channel to get new updates..!

Subscribe

Tips and tricks for creating calculations

Here are some tips and tricks that can help you while working with calculations. These will help you in creating effective calculations.

• Do not use the same calculated field in multiple calculations. Doing so will result in performance issues.
• Instead of using multiple equality expressions, use a CASE expression.
• Use REGEXP expression instead of using repetition expressions.
• Do not use sets in calculations, but replace them with alternatives.
• Do not group your data with sets.
• Do not convert sets to groups, because they cannot always be translated into groups.
• LOD expressions might rise performance issues sometimes. In that case, try replacing them with a table calculation.
• The data type that you use in the calculation impacts on calculation speed.
• Numeric calculations are much faster than string calculations.
• The ELSEIF works faster than nested ELSE IF in complex logic statements.
• Parameters might affect cache performance.
• All the basic functions pass through the underlying data.
• If you want time stamp level of detail, use NOW. If you want date level calculations, use TODAY.
• When you filter through complex calculations, the indexes might get missed in the underlying data.
• You can drag and drop a part of the calculation from the calculation editor into the Measures section of the Data pane to create a new calculated field.
• While you type in calculations in the calculation editor, Tableau suggests formulas.
• You can browse through the available functions using the functions reference.
• While creating calculations, you can also see which sheets will get affected when you apply the changes.

Click here to get latest Tableau Interview Questions and Answers for 2022

Conclusion

The type of calculation that you have to use for your data analysis typically depends on your data, the granularity of the visualization, and the output that you want to get. Ranking, recursion, moving calculations, inter-row calculations can only be performed with table calculations. So, analyze your dataset and your end goal before choosing a calculation type. Tableau also provides a parameter control through which users can determine how a calculation has to perform. Since you are familiar with calculations now, try them out in your visualizations in Tableau.

Related Articles:

• Tableau Data Blending
• Tableau Dashboard
• Tableau Forecasting
• Tableau Vs QlikView
• Tableau Vs Salesforce