Skip to main content
Back to path

Welcome to a Grasp lesson

Create a free account to keep studying and create your own study path.

Lesson 59

Creating Calculated Fields

Recommended resources

Hello! Welcome back to our module on Data Visualization and Storytelling with Tableau.

In our last lesson, you built a solid foundation by creating fundamental visualizations like bar charts, line graphs, and scatter plots using the existing data fields. You learned how to drag dimensions and measures onto the shelves to answer specific business questions.

But what happens when the exact metric you need for your analysis doesn't exist in your dataset? For instance, your data might have Sales and Profit, but you need to analyze Profit Margin. This is where the true power of business intelligence tools comes into play. Today, we'll tackle this by focusing on the learning outcome: to build calculated fields to create new metrics for analysis.

This skill is absolutely essential for a Business Analyst. You will constantly be asked to derive new Key Performance Indicators (KPIs), create custom segments, or clean up data to make it usable. Calculated fields are your primary tool in Tableau for accomplishing these tasks.

1. What are Calculated Fields?

Simply put, calculated fields allow you to create new data from your existing data. They are formulas that you define, which Tableau then uses to compute a new column (or "field") in your data source. This can range from simple arithmetic to complex logical statements.

You can use them to:

  • Create new measures, like calculating a profit ratio.
  • Create new dimensions, like segmenting customers into tiers based on their spending.
  • Clean or reformat data, like combining First Name and Last Name fields.
  • Convert data types, like turning a text string into a date.

To get a feel for what they are and how to create one, let's start with a quick video.

Learn Tableau Basic Calculations (Calculated Fields) in Tableau (with 10+ examples) | Part 1

This video from the sqlbelle channel provides an excellent overview of what calculated fields are and demonstrates the different ways to open the calculation editor in Tableau.

Watch the first 6 minutes of the video (00:00 to 06:12). Focus on: The explanation of why we need calculations (00:40). The different components of the calculation editor (02:00). The various methods to create a new calculated field (03:15). The helpful functions pane and how to use it (03:30).

2. Creating New Measures: The Power of Aggregation

The most common use of calculated fields is to create new measures (the green pills). Let's start with a classic business metric: Profit Margin.

Tableau Fundamentals: An Introduction to Calculated Fields

This article from ConcordUSA provides a clear, step-by-step guide to creating your first calculated fields. We'll focus on creating a new measure for Average Order Value (AOV).

Read the sections that guide you through creating two new measures: Start by reading the section that begins with 'To bring the concept of calculated fields to life...'. This shows how to create a distinct count of orders using COUNTD([Order ID]). Continue to the next section to see how this new measure is used to calculate Average Order Value (AOV) with the formula SUM([Sales]) / [Orders]. Pay close attention to the explanation of how Tableau handles aggregations within the formula.

Row-Level vs. Aggregate Calculations: A Critical Distinction

The AOV example introduces a crucial concept. When you create a calculation, you must decide whether it should operate at the row level (for every single record in your data) or at an aggregate level (summing or averaging records first, then calculating).

  • Row-Level Calculation: [Profit] / [Sales]. This calculates the profit ratio for each individual transaction. If you then bring this field into a view, Tableau will aggregate the results (e.g., it will SUM up all the individual ratios, which is usually incorrect).
  • Aggregate Calculation: SUM([Profit]) / SUM([Sales]). This first sums up all profit, then sums up all sales, and then performs the division. This is almost always the correct way to calculate ratios.

Getting this wrong is one of the most common mistakes for new Tableau users. The following video provides a perfect demonstration of why this is so important.

Learn Tableau Basic Calculations (Calculated Fields) in Tableau (with 10+ examples) | Part 1

This clip is the most important part of this lesson. It clearly demonstrates the difference in results between a row-level and an aggregate calculation for a profit ratio, and why the aggregate one is correct when data is rolled up.

Watch the segment from 20:43 to 23:39. Observe what happens to the two different 'Profit Ratio' calculations when the level of detail in the view changes (when the Invoice Line ID is removed).

Test your understanding!

You are working with an AdTech dataset that contains Impressions and Clicks for every ad shown. You need to create a new metric for Click-Through Rate (CTR), which is defined as (Total Clicks / Total Impressions).

Which of the following formulas should you use for your calculated field, and why?

  1. [Clicks] / [Impressions]
  2. SUM([Clicks]) / SUM([Impressions])
Show answer

You should use formula 2: SUM([Clicks]) / SUM([Impressions]).

This is an aggregate calculation. It correctly calculates the CTR across any dimension you might use (e.g., by campaign, by date, or for all ads). It first sums up all the clicks and all the impressions in the view and then divides the totals.

Formula 1 is a row-level calculation. It would calculate a CTR for each individual ad impression (which would be either 0 or undefined in most cases), and then Tableau would try to aggregate these individual results later (e.g., by summing or averaging them), leading to a meaningless number.

3. Creating New Dimensions with Logic

Calculated fields aren't just for numbers. You can also create new categories or segments using logical functions like IF/THEN/ELSE. This is extremely useful for grouping data in new ways.

For example, you could write a calculation to classify sales as 'Large' or 'Small'.

IF SUM([Sales]) > 1000 THEN 'Large Sale'
ELSE 'Small Sale'
END

Let's see this in action. The following resources show how to use IF statements to create categorical fields.

Calculated Fields in Tableau (Formulas & IF Statements) - Tableau Tutorial P.6

This short video by Luke Barousse provides a very clear example of using an IF statement to classify transactions as either 'Profit' or 'Loss'.

Watch the section from 02:35 to 04:32. Notice how he uses the IF keyword, a condition ([Profit Margin] >= 0), a THEN result, an ELSE result, and the final END keyword.

You can create even more complex segments by adding OR conditions. This is a powerful way to create custom groupings relevant to your business analysis.

Tableau Fundamentals: An Introduction to Calculated Fields

Now, let's look at a slightly more advanced example from the ConcordUSA article. This demonstrates creating a custom segment for analysis.

Read the section titled 'What is the AOV of the product subcategories we manage...'. Observe how the formula uses OR to group multiple sub-categories ('Copiers', 'Machines', 'Supplies') into a single segment called 'My Sub-Categories'.

4. Working with Strings and Dates

Often, your data isn't perfectly formatted. You might need to join text fields together or calculate the duration between two dates. Calculated fields have a rich library of functions for this.

String Functions

These functions manipulate text. Common uses include:

  • Concatenation: Joining strings together (e.g., [First Name] + " " + [Last Name]).
  • Splitting: Extracting parts of a string (e.g., getting the domain from an email address).
  • Changing Case: UPPER() or LOWER().

Date Functions

These functions perform operations on dates. Common uses include:

  • Creating Dates: MAKEDATE(year, month, day).
  • Calculating Duration: DATEDIF('day', [Start Date], [End Date]) calculates the number of days between two dates.
  • Parsing Dates: DATEPARSE() can convert text that looks like a date into a proper date field.

The following video demonstrates several of these useful functions.

Learn Tableau Basic Calculations (Calculated Fields) in Tableau (with 10+ examples) | Part 1

This video clip from sqlbelle will walk you through practical examples of both string and date calculations.

Watch the following two segments: String Functions (07:35 - 09:25): Focus on the example of creating a full name by concatenating first and last names. This is a very common task. Date Functions (12:07 - 12:55 and 15:10 - 16:16): First, watch the MAKEDATE example to see how to construct a date from separate year/month/day fields. Then, watch the DATEDIF example to understand how to calculate the difference between two dates.

Conclusion

Great job! You've added one of the most powerful tools in Tableau to your analyst toolkit. You've gone beyond just visualizing the data you're given and can now create the specific metrics and dimensions your analysis requires.

Key Takeaways:

  • Calculated Fields allow you to create new dimensions and measures from existing data using formulas.
  • Row-Level vs. Aggregate Calculations: The distinction is critical. For ratios and most KPIs, use aggregate calculations (e.g., SUM([Sales]) / SUM([Orders])) to ensure accuracy when your data is summarized.
  • Logical Functions (IF/THEN/ELSE) are used to create new categories and segments, allowing you to group your data in custom ways.
  • String and Date Functions are essential for cleaning, formatting, and performing calculations on text and date fields.

Preview of the Next Lesson:

You can now connect to data, create basic charts, and even invent your own metrics with calculated fields. So what's next? Bringing it all together. In our next lesson, you will learn how to design and build an interactive dashboard with filters and actions. You'll combine multiple worksheets into a single, cohesive view, empowering your stakeholders to explore the data and find their own insights.