Tableau Calculated fields can be used to create new dimensions such as segments, or new measures such as ratios or sums. They can be used with any data type, various functions and aggregations, logical operators, making the calculated results virtually limitless. In this blog, we will be introducing Tableau Calculation fields by walking through the following topics.
Check out this video to get started with concepts of Tableau calculations.
Why use Tableau Calculation Fields?
Before connecting data sources to Tableau, it is always suggested to collect as much data as possible. Shouldn’t this data include all of the required fields for the analysis then?
There are many reasons for using calculation fields functionality in Tableau. The below are a few of them:
- To use different filters to remove unwanted results for better analysis
- To segment data in new ways
- To calculate ratios across different variables in Tableau, saving database processing and storage resources
- To convert the data type of a field, such as converting string to date
- To aggregate data
Types Of Calculations
Calculated Fields in Tableau are created using calculations. The calculations in tableau which can be used to create calculation fields are categorized into three classes which are mentioned below.
Basic Calculations
These calculations allow us to transform values at a data source level of detail or at a visualization level of detail(aggregate calculation).
Level Of Detail Expressions
The second way of creating a calculating field is by using LOD expressions. LOD calculations allow you to compute values at the data source level and visualization level, as well as give us even more control over the level of granularity we want to compute.
Table Calculations
These calculations allow us to transform values at the visualization level of detail only.
The choice of calculation depends on the type and requirements of your analysis.
How To Add Calculated Field in Tableau
In this example, we are using the Sample-Superstore data source that comes with Tableau Desktop to create a Calculated Field.
- Connect to the saved data source “Sample-Superstore” and move to sheet-1.
- Select Analysis >> Create Calculated Field.
- In the calculation editor, implement the following steps:
- Enter a name for the calculation field. In this example, the field is called the Profit ratio.
- Enter a formula that represents your logic. For this example, the following formula is used
- This formula gives us the profit ratio of each sub-category.
The new Calculation field is added to Measures in the Data pane as shown below.
Tableau Conditional Calculated Field
Sometimes, during data analysis, we only require certain values of a particular field. This can be implemented by creating a Tableau conditional calculated field. Using these fields you can create conditional data labels on a custom field that includes only values of interest.
Get started with Tableau Certification Training course and become a certified professional.
Tableau Calculated Field If-Then Statements
If statements are a fundamental part of Tableau or any other analytics platforms and programming languages. Understanding what Tableau calculated IF-THEN statements do and how they work is critical when building calculated fields.
The IF keyword tells Tableau that we are about to perform an IF statement. THEN indicates that we are about to specify some return value. In between IF and THEN is what is referred to as a conditional expression. When these expressions are used in the calculation field it becomes a Conditional Calculated Field.
Curious about Power BI? Here is the Power BI Course in Bangalore provided by Intellipaat.
How to Create a Conditional Calculated Field
The following are the steps to create a conditional calculated field.
- Select Analysis >> Create Calculated Field.
- In the Calculated Field dialog box:
- Name the calculated field. In this example, the calculated field is named “Outbound”.
- In the formula field, create the following formula.
- Click OK. These conditional fields are then used to build the view in the tables.
Tableau Calculated Field Order Of Operations
Now that we know about IF statements, let’s learn a bit more about some complex logical issues we may encounter. For example, consider the following expression
IF[Category]= “Office Supplies” OR [Category]= “Technology” AND [Order Date] >= #01/02/2020# THEN
When we have an expression consisting of multiple logical expressions, the order of precedence plays an important role. The order of operations is just like the one used in equations in mathematics. The precedence order of the operations is as follows:
- Parentheses
- NOT
- AND
- OR
Hence, the first thing to be computed in a logical statement is the parentheses, which group comparisons together. The next one is NOT, followed by AND then OR. If we apply for this order in the above example, the two comparisons separated by AND will be evaluated first.
[Category]= “Technology” AND [Order Date] >= #01/02/2020#
This expression result gives us anything with a category of “Technology” and an order date on or after February 1, 2020.
The OR will then act as if the above statement is a single expression. So, in layman terms, the original statement will give us anything where the category is “Office supplies” OR the category is “Technology” and the order date is on or after February 1, 2020.
Since it can be difficult to think through the order of operations while writing an IF statement, it is recommended to always use parentheses to group your conditions.
There is more lot to learn than this, enroll now in Tableau Online Training in Bangalore.
Get 100% Hike!
Master Most in Demand Skills Now!
How To Do Group By in Tableau Calculated Field
In Tableau, we can create a group to combine related members in a field. For instance, if we are working with a view that shows average sales by sub-categories, we might want to group certain sub-categories together. Groups are useful for both correcting data errors and are also helpful in answering “what if” type questions.
Now, let’s create a group to understand the concept in more detail.
- In the Data pane, right-click a field and select Create >> Group.
- In the create group box, select different members that you want to group and then click Group.
- The selected members are thus combined into a single group. A default name is created using combined member names.
Check out the list of Tableau Developer Interview Questions!
Tableau calculated field sum by groups
This calculation field can be used for instances like to find the sum of sales for each category. This example can be implemented as follows.
- Click on Analysis >> Create Calculated Field
- Name the field and implement the calculation in the field
- Click OK
Tableau Create Bins From Calculated Field
In Tableau, we can create bins from a calculated field. Any discrete field in Tableau can be considered as a set of bins. For instance, if we create a view with profit as a row and state as a column, we can consider the state field as a set of bins, that is each profit value is sorted into a bin corresponding to the state from which the value was recorded.
Let’s study how to create bins from a measure calculated field in Tableau.
- In the Data pane, right-click on a measure and select Create >> Bins.
- In the dialog box, accept the proposed new field name.
- You can enter a value in the Size of bins field or have Tableau calculate a value for you. The default Size value of the bin is 10. If your system is not efficient in optimizing the value quickly, Tableau shows default bin size, in that case, you can click Suggest Bin size to have Tableau perform the optimizing calculation.
- The four read-only fields in the lower part of the Create Bins dialog box show the data that Tableau uses to suggest a bin size.
Click OK and you will see a new binned field will appear in the Dimensions area of the Data pane.
When we add a binned dimension to the view, each bin acts as an equal-sized container that totals data for a specific range of values. These binned dimensions are further used to create histograms.
Prepare yourself for job interviews by learning from our Power BI interview questions for Freshers.
Tableau Sort By Calculated Field
In Tableau, it is possible to create custom and dynamic sorts by using a mixture of parameters and calculated fields. To sort by a calculated field there are 2 options:
- Use the field in the sort – field section of the field sort menu
- Place the sorting calculated field as a discrete pill to the left of the fields to sort
In this example, we are using Superstore Sales sample data that Tableau provides.
- The first step is to create a parameter called Sortusing. This contains the names of the fields for the user to select.
- Create another parameter, OrderofSort, which allows the user to choose whether to sort Ascending or Descending.
- Since the first step is done, now we have to create a calculated field using these parameter values.
- To implement the sort, you can click on sort under required field and select for the field, and opt for “Sortfields”.
Date Difference in Tableau Calculated Field
One of the interesting functions in Tableau is the DATEDIFF function. When this function is used within a calculated field, you can quickly start calculating date differences in Tableau using two date fields. Let’s see the following example along with implementation to understand in a better way.
So, in this example, we are calculating the difference between the order date and shipping date, as its analysis can help us in enhancing effective delivery time.
- Drag and drop product names, order date, and ship date to the rows shelf
- Opt for the “exact date” in the dropdown of both the dates field
- Set both of them to discrete
- Now, create a calculated field named Time Taken to Ship and write the following calculation.
- Use this calculated field to sort the product name as follows.
Hence, we can use the Date difference feature for very useful data analytics and visualization.
Exclude in Tableau Calculated Field
Exclude level of detail expressions prevent the calculation from using one or more of the dimensions that are present in the view. These expressions are especially useful for ‘percent of total’ or ‘difference from overall average’ situations.
For instance, if we want to calculate the sum of sales by excluding a particular region, we can follow the given steps and create a new calculated field “Region Excluded”.
- Click on Analysis >> Create Calculated Field
- Name it as “Region Excluded”
- Implement the following expression in the dialog box
This field, thus, can be further used to sort or arrange other dimensions during the visualization and analysis of the data.
In this way, the Calculated fields in Tableau are of great help in implementing efficient and accurate data analytics. We hope this article has shed some light on various concepts of Calculated Fields in more detail.