Understanding CALCULATED FIELDs in TABLEAU

A blog intended for newbies and those who thrive to produce better insight with the help of Calculated Fields. 

Calculated Field as the name suggests is a user defined calculation used to generate new data from the existing data source for the purpose of developing desirable insights. Essentially bridging the gap between raw data and analytical requirements of specific reports or visualization.

Here is a brief explanation about Tableau calculation: how to create and edit them.

METHOD 1

METHOD 2

Why use Calculated Fields ?

Calculated Field allows us to create new data from the one (data) that already exists in the data source. When we write a Calculated Field, a new field (a column) is created in the data set with values which are determined by user-controlled calculations.

Here are few of the many reasons behind the creation of Calculated Fields:

·       To segment data

·       To convert the data type of a field such as converting string to date.

·       To aggregate data

·       To filter data

·       To calculate ratios

·       To manipulate dates such as extracting year month or day from date field or calculating date difference.

Types of Calculations

There are three main types of calculations in Tableau:

• Basic Expressions:

  •  Row-level calculations

  •  Aggregate calculations

•  Table calculations

•  Level of Detail (LOD) Expression

  •  Fixed LOD

  •  INCLUDE LOD

  •  EXCLUDE LOD

Let’s explore each calculation one by one.

NOTE: To elaborate the calculations, here I used the Sample-Superstore data source that comes with Tableau Desktop and can also be downloaded from this link: https://www.kaggle.com/datasets/namratakapoor1/superstore

Creating a Calculated Field

 Basic Expressions : This calculation allows us to transform data at the data source level (known as Row-level calculations) or at the visualization level (also called Aggregate-level calculations).

Row-level calculations

1.       After loading the dataset, calculated fields can be created in two ways:

I. Go to Sheet1 --> Analysis from Menu bar --> Create Calculated Field or

II. From the leftmost pane, beside the Search tab  à Click on Drop Down icon (select 1st option) à Create Calculated Field.

2.       In the calculated editor that pops up, enter a name for the calculation e.g.  Sales + Tax.

 3.       After clicking OK, “Sales+Tax” field will appear as a new column in the data pane and can be used in the view to create table or visualization. Below is the output screenshot.

This calculation results in a single aggregated value “SUM” and is performed at the row level by adding all the sales value in that row. Hence the name row-level calculation.

The type of aggregation can be changed by clicking the drop-down arrow beside the field name under the Measure Values and selecting a different aggregation from the context menu.

Tips:

o   Unlike excel, it does not have an equal to sign at the beginning.

o   One or multiple comments can be added for readability and these should start with //.

o   When referencing other fields in the dataset, the column name appears within parenthesis like ([Sales]).

o   List & description of all available functions can be seen by clicking the triangle icon from the right side of the Calculation Editor. 

o   Clicking on  “1 Dependency” will list all the places this calculation is used such as sheet name, nested calculations etc.

Aggregate-level calculations

Specifying the desired aggregate function in the Calculated Field itself is an alternative to the above method of selecting them from the view level. The calculation below returns the average sales value per category regionwide:

NOTE: The aggregation type cannot be changed from the view by right-clicking. It can only be modified by clicking the “Edit” tab from the drop-down icon besides the “Measure Names” as shown below:

Now if we want to calculate the sum/average of sales inclusive of all categories and sub-categories we use window aggregate functions in Table Calculation.

 Table Calculation

These calculations are a sub-set of Calculated Fields created to transform values into a visualization (not considering any measures or dimensions). They are special types of Calculated Field that computes on local data and can be used to reference other values in the virtual table required for computing Running Sums, Moving Averages, Percentages of Totals.

WINDOW AVERAGE

When we add a table calculation using the Compute Using options, Tableau identifies some dimensions as Partitioning (i.e. the scope of the data it is performed on)  and some as Addressing (determining the direction of the calculation) automatically. But when we use Specific Dimension, then we can define and control  the dimensions meant for Partitioning and Addressing.

By default, the windows calculation is “Table (down)”, meaning the calculation is performed across the full virtual table in a downward direction but can be changed based on requirement as shown below:

CATEGORYWIDE

TABLE DOWN

REGIONWIDE

 Level of Detail Expressions

This type of Calculated Field allows us to compute values at the data source level and then use those values in visualization. LOD calculations give us more control on the level of granularity we want to compute.

LOD’s can be classified into three different types:

·       Fixed LOD – Calculates the value at a specified level of detail independent of the view.

·       INCLUDE LOD – calculates the value at a more granular level of detail than the view.

·       EXCLUDE LOD – calculates the value at a less granular level of detail than the view.

INCLUDE LOD

Here both the LOD expressions return same value as the table calculation but with only Region dimension in the view:

FIXED LOID

That's all about Calculated Fields in Tableau. Will write up soon on some other interesting topics on Data Analysis.