Calculated Fields

Creating custom calculated fields in Tableau — basic arithmetic formulas (e.g., a Cost field using SUM([Sales]) - SUM([Profit])), MIN/MAX aggregation functions, and CEILING/FLOOR functions for numeric rounding and formatting.

10 minutesVideo LessonPDF notes

🎯 Free Guest Mode: You are learning for free. Sign in to save your completion progress and quiz answers.

Ready to continue?

Mark this lesson as complete when you're ready to proceed.

Key moments

Introduction & Setup — Loads the Global Superstore Orders table and adds Sub-Category, Sales, and Profit to the view.
Cost Calculated Field — Creates the Cost measure using the formula SUM(Sales) - SUM(Profit) and explains the calculation editor interface.
Calculated Field Icon — Explains how the '=# ' icon distinguishes custom calculated fields from native data source fields.
Disaggregating Measures — Uses the Analysis menu to uncheck Aggregate Measures and inspect row-level sales transactions for products.
MIN and MAX Calculations — Builds MIN(Sales) and MAX(Sales) calculated fields and demonstrates resolving syntax errors during entry.
CEILING and FLOOR Functions — Formats measures to two decimal places and demonstrates rounding numbers upward with CEILING and downward with FLOOR.

PDF notes

Bottom line: You will create custom calculated fields in Tableau to derive new metrics not present in the raw data source. You will learn to use basic arithmetic, aggregations like SUM, MIN, and MAX, and rounding functions like CEILING and FLOOR to control how numbers display in your visualizations.

This lesson on Calculated Fields is hands-on and example-driven. You will create custom calculated fields in Tableau to derive new metrics not present in the raw data source. You will learn to use basic arithmetic, aggregations like SUM, MIN, and MAX, and rounding functions like CEILING and FLOOR to control how numbers display in your visualizations.

What You'll Be Able To Do

Create new calculated fields using the Tableau calculation editor and autocomplete.
Derive calculated measures such as Cost using arithmetic subtraction across aggregated fields.
Toggle Aggregate Measures in the Analysis menu to inspect row-level values against aggregated totals.
Implement MIN and MAX functions to evaluate extreme values across dimensional categories.
Apply CEILING and FLOOR functions to round floating-point measures to the nearest upper or lower integer.

Detailed Concept Walkthrough

1. Creating Calculated Fields and Visual Identifiers

Calculated fields allow you to create new data dimensions or measures dynamically using formulas evaluated at query time. Tableau visually distinguishes custom calculated fields from native data source fields using a specific icon prefix.

Mechanism: You open the calculation editor by clicking the drop-down on any measure or dimension in the Data pane and selecting Create > Calculated Field. The editor provides a function reference pane on the right that categorizes functions into Number, Date, Logical, and other groups.
Under the Hood: Native database measures display a plain '#' or 'Abc' icon in the Data pane. When you save a custom calculation, Tableau prefixes the icon with an equals sign (e.g., '=#'), indicating that the field is derived dynamically at runtime rather than stored in the underlying data source.
Best Practice: Use keyboard autocomplete (typing a function name and pressing Tab) to insert functions and existing measure names accurately without syntax errors.

// Calculated field: [Cost]
// Calculates total cost by subtracting aggregated profit from sales
SUM([Sales]) - SUM([Profit])

Key Takeaway: Calculated fields are derived metrics marked with an '=# ' icon that execute dynamically across your visualization.

2. Aggregation and Disaggregation of Measures

By default, Tableau rolls up row-level data to the visualization's level of detail using aggregate functions like SUM, MIN, and MAX. Disaggregating data reveals the underlying row-level records that contribute to the aggregate figure.

Mechanism: Adding a measure to a view automatically wraps it in an aggregation (such as SUM(Sales)), summing all row-level transactions for that category. Navigating to the Analysis menu and unchecking 'Aggregate Measures' temporarily expands the view to display every individual row-level transaction separately.
Under the Hood: Aggregation executes a GROUP BY operation behind the scenes at the granularity defined by the dimensions in the view. When disaggregated, Tableau issues a query returning raw data rows without grouping, allowing you to see the exact minimum and maximum row-level transaction amounts.
Syntax Rule: When referencing fields inside standard aggregate functions like MIN or MAX, pass the measure directly inside the parenthesis without nesting extra SUM calls unless creating multi-level calculations.

// Calculated field: [Minimum Sales]
// Returns the smallest individual transaction value for the grouped dimension
MIN([Sales])

// Calculated field: [Maximum Sales]
// Returns the largest individual transaction value for the grouped dimension
MAX([Sales])

Key Takeaway: Disaggregating measures reveals individual row-level records, while MIN and MAX isolate extreme transaction values across dimensional groupings.

3. Mathematical Rounding with CEILING and FLOOR

CEILING and FLOOR provide deterministic rounding control over floating-point numeric expressions. CEILING always rounds up to the next highest integer, while FLOOR always rounds down to the nearest lower integer.

Mechanism: The CEILING function maps any numeric expression with a fractional component to the nearest integer greater than or equal to the argument. Conversely, the FLOOR function truncates the fractional part, mapping the value down to the nearest integer less than or equal to the argument.
Under the Hood: Both functions evaluate the aggregated or row-level numeric expression passed to them and return an integer value, stripping decimal precision while preserving the underlying aggregate computation.
Best Practice: Format your input measure to display decimals (e.g., Number Custom with 2 decimal places) when verifying that CEILING and FLOOR calculations behave as intended across test values.

// Rounds 681.76 up to 682
CEILING(SUM([Sales]))

// Rounds 681.76 down to 681
FLOOR(SUM([Sales]))

Key Takeaway: CEILING rounds decimal values strictly upward to the next integer, while FLOOR rounds strictly downward.

Topics Covered in Calculated Fields

Introduction & Setup (0:00 - 0:45) — Loads the Global Superstore Orders table and adds Sub-Category, Sales, and Profit to the view.
Cost Calculated Field (0:45 - 2:10) — Creates the Cost measure using the formula SUM(Sales) - SUM(Profit) and explains the calculation editor interface.
Calculated Field Icon (2:10 - 2:35) — Explains how the '=# ' icon distinguishes custom calculated fields from native data source fields.
Disaggregating Measures (2:35 - 3:30) — Uses the Analysis menu to uncheck Aggregate Measures and inspect row-level sales transactions for products.
MIN and MAX Calculations (3:30 - 4:40) — Builds MIN(Sales) and MAX(Sales) calculated fields and demonstrates resolving syntax errors during entry.
CEILING and FLOOR Functions (4:40 - 6:05) — Formats measures to two decimal places and demonstrates rounding numbers upward with CEILING and downward with FLOOR.

Reference Cheat Sheet

SUM([Field]) - SUM([Field2]) — Subtracts one aggregated measure from another
```
SUM([Sales]) - SUM([Profit])
```
MIN([Field]) — Returns the minimum value of a measure
```
MIN([Sales])
```
MAX([Field]) — Returns the maximum value of a measure
```
MAX([Sales])
```
CEILING([Expression]) — Rounds a number up to nearest integer
```
CEILING(SUM([Sales]))
```
FLOOR([Expression]) — Rounds a number down to nearest integer
```
FLOOR(SUM([Sales]))
```

Comparison Table

Function / Feature	Output Behavior	Typical Use Case
SUM([Sales])	Totals all values	Calculating total sales volume
MIN([Sales])	Smallest individual value	Finding lowest transaction amount
MAX([Sales])	Largest individual value	Finding peak transaction amount
CEILING(SUM([Sales]))	Rounds up to integer	Upper-bound integer estimates
FLOOR(SUM([Sales]))	Rounds down to integer	Lower-bound integer truncation

Common Pitfalls

Mistake: Nesting SUM improperly inside MAX like MAX(SUM([Sales])). Avoid: Pass the raw measure directly to the aggregation function as MAX([Sales]).
Mistake: Confusing native database fields with calculated fields in the Data pane. Avoid: Look for the equals sign prefix (# vs =#) to identify calculated fields.
Mistake: Expecting CEILING or FLOOR to maintain decimal places in the visualization. Avoid: Recognize that CEILING and FLOOR return whole integers regardless of input decimals.

FAQs

How can I tell if a field in the Data pane is native or user-created? Calculated fields have an equals sign prefixed to their data type icon (e.g., '=#') in the Data pane.
Why does MAX(SUM([Sales])) produce a calculation error? Tableau does not allow basic aggregate functions to be nested directly without using table calculations or Level of Detail expressions; use MAX([Sales]) instead.
What happens when I toggle 'Aggregate Measures' in the Analysis menu? Unchecking 'Aggregate Measures' disaggregates the view to display every individual underlying row from the data source rather than summing them.

Common pitfalls

Mistake: Nesting SUM improperly inside MAX like MAX(SUM([Sales])). Avoid: Pass the raw measure directly to the aggregation function as MAX([Sales]).
Mistake: Confusing native database fields with calculated fields in the Data pane. Avoid: Look for the equals sign prefix (# vs =#) to identify calculated fields.
Mistake: Expecting CEILING or FLOOR to maintain decimal places in the visualization. Avoid: Recognize that CEILING and FLOOR return whole integers regardless of input decimals.

In this video we'll see how to work with calculations in Tableau. If you have used Microsoft Excel, and there are some in-built functions by Microsoft in Excel like Sum, Minimum, Maximum, Average, you can calculate the difference. So there are all the functions whatever you have in Excel, similar to that you have in the Tableau part. So let us see some few calculations which are basic in Tableau for the first time. Now you can download this workbook below this video. The name of the workbook is Calculations, and the sheet name, that is the database name is Global Superstore 2017 Excel file. I've picked up the Orders table here. Now what I'll do is, I'll try to create some calculations, but for that, I need to have some data here. So first thing is I'll just double-click on this Sub-Category, double-click on this Sales part, and after that I'll just double-click on this Profit here. So now if you see, I have the Profit and Sales with respect to the Sub-Category which is given here. So what I'll do is I'll try to make into the descending order first. I'll select this, say as Descending order Sales. I'll expand this option, expand the data here. Yeah. Now what I'm what I'll be doing is here is I want to calculate Cost of the Sub-Category. When can you calculate the Cost? When you have the sum, if you subtract the Profit from there, you'll be getting a Cost. So I have the Sales, if I subtract the Profit from here, I'll be getting a Cost at this place. But there is no Cost measure in this place. If you observe carefully, there is no Cost. So what I can do is, I can manually create a Cost field, that's a Cost measure, which will subtract Sales minus Profit, and it should give me a Cost here. Creating a field manually, it's very simple in this Tableau part. How do you create it? You select any measure from here, like this, and you click on this drop-down, any measure you can select. You can click on this option as Create, and the given app option is given as Create Calculated Field. Select this one, you'll get a dialog box. Here you can give the name of the field, here you can do the calculations part. On the right-hand side if you see, you'll be getting different types of calculations in Tableau. For example, if I click on this drop-down, I'll be getting number-related calculations, if I click on this, I'll be getting sum, minimum, maximum, tan, cos, theta, all these options. If I click on this drop-down, date-related functions, like find out the day and find out the month, year, quarters. And click on the drop-down, there are different types of logical and so on. So you can use any one of them. If you want to hide and show, you can just click on this button, it will show and hide the this one this dialog box. If you click on this Show dialog box, it will help you to write the, you know, calculation. This is the syntax which you can work on. Right now what we're doing is, we want to find out the Sales minus Profit and I'll be getting a Cost here. What is the name of the field you want to give? I want to give the name of the field as Cost, and what exactly you want to write here is, I want to say I want to subtract SUM of Sales. Okay. How do I write it? You just type as SUM, press Tab. When you write down SUM, you'll get the options and functions which are given here. You press Tab from your keyboard. What is the expression you want? You just type as Sales. When you type Sales, it says there is already a measure, if you see there's a green-color measure, Sales, which is already present. As soon as you see that, press Tab from your keyboard, like this. And after that, I'll press subtract symbol. SUM of, press Tab, again Profit. Scroll down, I mean you can just use your arrow keys to come down, and let's say if I write as Profit and press Tab key from keyboard. Remember, whenever you find out this Sales, these are already in-built in the measures itself, you can use those one. And you have to press OK. Apply and OK. Here if you observe, I've got a Cost measure. There's a small, slight difference between this discount symbol and this one. Why? There's an equal hash sign, and here it's only hash sign. Equal hash sign says it is a calculated field, it is not the automatic generated field or it's not a database field itself, it is now newly created field. So what you can do is, you can just pick up this Cost and you can drop into this place itself, like this. I'll just pick up the Cost, and I'll drop into this window here. So if you find out, if you see this, Sales minus Profit and Cost. Let's try to calculate roughly. If you subtract 73 If you subtract 14,000 from 73, you'll get this answer as 53 58,361. That is nothing but the Cost. So like this, you can keep on creating multiple calculated fields. Let's see some more few examples. I want to calculate minimum and maximum of some particular field. So first of all, I'll pick up this as the Product Name. I'll pick up the Product Name, double-click on the Product Name and double-click on the Sales part. So here it says there is there are multiple products, if you see, there are multiple products which are given here. Now in this products, the total Sales is 2706. Now 2706 is nothing but sum of the Sales of all the product, you see, sum of Sales of all the product. This product might be sold 10 times, 20 times. If you want to find out what was the minimum and maximum, but before that, I'll show you how come 2706 came. So for that, you have to go to the Analysis menu bar, and you have to go to this Analysis menu bar and you have to select this option as Aggregate Measures. So now Aggregate Measures, if you see, it says I'll just expand so it is visible properly here. I'll just expand this field, any one field you can expand. Yeah. Now when you expand, it says this product, 1.7 Cubic Foot, it is 1041, 500, minimum was 208, and maximum was 1041. It has been sold 1, 2, 3, 4, 5, 6, 7 times it has been sold, and the maximum was 1041 and the minimum was 208. So this was the entire detail about it. Same way, this was the minimum was 6 and this was the maximum which was sold, that the price amount was sold. So what I can do is, I have an option where I can find out minimum and maximum with some function, that is by creating a calculated field. So first of all, I'll come back to the original part, Analysis menu bar, and I'll click on this Aggregate Measures. This is the 2706. If you combine all of them, it gives you 2706. I'll create a calculated field where I can show minimum and maximum. Click on any field, click on this drop-down, click on this Create, Calculated Field. And here I'll type as Minimum, and down I'll type as MIN Sales, that's it. Minimum of Sales and I'll click on Apply, click on OK. Again, if I want, I can just pick up this Minimum and I can drop it in this place, like this. And if you see, it says the minimum is 208, and here also the minimum is 6. Same way, I'll try to create a Maximum. Click on this drop-down, click on this Create, Calculated Field. Type as Maximum. If you want, you can give the name of the field anything. I'll type as MAX of sum MAX of sum and click on Yeah, there's a mistake here. If you see, when you keep on typing, you'll get an error message: The calculated field contains error. So what is the error? It says: SUM is being called with brackets. Did you mean by float? So I said, this is the incorrect which I have written. So what I have to write as: MAX, bracket open, I have to type as Sales, and then you have to press Tab key from keyboard. And now see, there is no error message which I have got. Click on Apply, click on OK, and I'll be getting a Maximum, pick up and drop it. Remember, the 14,000 was the number, 1400 1041, that was the number. So the minimum Sales done was the 208, and the maximum amount of Sales it was done was 1041. So this way, you can keep on creating a calculated field. Now let's see how to work on the Ceiling and Floor. So I'll select this Ceiling and Floor, and now what I want is, I'll just pick up this Sub-Category, and I'll pick up this as Sales. So if you see here, I've got the numbers here which are not in the ascending or descending order, so I'll make into descending, select and say as Descending order which is given here. But I want, if you want Ceiling and Floor, you should have some decimal numbers in this example. So for that, I need to format. I'll just click on this drop-down, click on this Format, click on this format pane default, I'll just click on this place and I'll say as I want the Number (Custom), and I'll select as 2 decimal, click outside. So if you observe here, I've got the numbers which are in two decimal. So I'll just click on this format pane which I've got it here. Now what I'll do is, I want to make into, you know, round off all these numbers. So how do I round off this numbers using this Ceiling and Floor? Create a calculated field where if the number is 824.14, it should be as 824 itself. If the number is 681.76, it should become as 682. So this is how I can make the Ceiling and Floor. So let's see. If I want to create a Ceiling, so I'll just click on this drop-down, click on this Create, Calculated Field. Here I'll give the name as Ceiling. Type here as in the pane, I'll type here as CEILING SUM of Sales. So I can type here, and I'll just click on this Apply and OK. So what I'll do is, I'll pick up this Ceiling, and I'll drop into this part itself. Now if you observe, this was the number: 681.76, it has been converted to 682, that's the ceiling part. Wherever you find number is 824.14, it will be converted to the upper one. Same way, if I want to the convert into the lower one, that is nothing but given as Floor itself. So for that, I'll just click on this drop-down, click on this place, click on Create, Calculated Field, and here I'll type as Floor. Floor. I'll type as FLOOR SUM of Sales. That's it. And I'll just click on this Apply and OK. So here there's a there's a measure which is created as Floor, so I'll pick up this Floor and I'll drop it in this place. So if you observe, I've got a Floor. All the numbers, if you see, it has taken to one level down itself. So whatever decimal it has got, it has rounded off to the lower one. So if you observe here, 681.76, it was the number, it has converted to 681 itself. So it has taken to the ground part itself. Same way, Ceiling will take you to the top, and the Floor will take you to the bottom part itself. So that is the method you can just keep on creating minimum, maximum, ceiling and floor, and also we have seen how to create the Cost field itself. So I hope you have understood the basics of creating a calculation field itself. That's all for this video.

Frequently asked questions

How can I tell if a field in the Data pane is native or user-created?

Calculated fields have an equals sign prefixed to their data type icon (e.g., '=#') in the Data pane.

Why does MAX(SUM([Sales])) produce a calculation error?

Tableau does not allow basic aggregate functions to be nested directly without using table calculations or Level of Detail expressions; use MAX([Sales]) instead.

What happens when I toggle 'Aggregate Measures' in the Analysis menu?

Unchecking 'Aggregate Measures' disaggregates the view to display every individual underlying row from the data source rather than summing them.

How was this lesson?

Your feedback helps us refine explanations and catch bugs.