Tableau Calculated Fields and LOD Expressions

Your data almost never has every number you need. It has Sales and Profit, but your manager wants profit margin, days to ship, the financial year, a running total and each customer’s first purchase date. In Tableau you create these yourself with calculated fields. In this lesson we start with simple formulas, then learn table calculations, and finish with Level of Detail (LOD) expressions, the feature that makes Tableau so strong for finance analysis.

What you will learn

• How to create a calculated field and read the formula syntax
• Profit margin the right way, plus IF, CASE, date and text functions
• Table calculations for a running total and percent of total
• LOD expressions with FIXED, INCLUDE and EXCLUDE, with customer examples
• Which kind of calculation to use for which question, and the common mistakes

Our sample data

This lesson uses one order list for a made-up wholesaler. Type it into Excel as a sheet named Orders, with real dates, and connect Tableau to it as shown in Tableau: Build Your First Dashboard. Amounts are in rupees.

Order ID	Order Date	Ship Date	Customer	Region	Category	Product Code	Sales	Profit
SO-2001	05-04-2025	08-04-2025	Mehta Stores	West	Furniture	FUR-CH-101	40000	6000
SO-2002	22-04-2025	24-04-2025	Kapoor Brothers	North	Technology	TEC-PH-201	25000	5000
SO-2003	10-06-2025	15-06-2025	Mehta Stores	West	Office Supplies	OFF-PA-301	10000	2000
SO-2004	18-07-2025	20-07-2025	Iyer Agencies	South	Technology	TEC-LA-202	60000	9000
SO-2005	02-09-2025	09-09-2025	Kapoor Brothers	North	Furniture	FUR-TA-102	30000	-3000
SO-2006	14-10-2025	16-10-2025	Desai Traders	West	Office Supplies	OFF-BI-302	8000	1200
SO-2007	25-11-2025	27-11-2025	Mehta Stores	West	Technology	TEC-PH-203	45000	6750
SO-2008	08-01-2026	12-01-2026	Singh Retail	North	Furniture	FUR-CH-103	35000	2800
SO-2009	19-02-2026	21-02-2026	Kapoor Brothers	North	Office Supplies	OFF-PA-303	5000	900
SO-2010	27-03-2026	30-03-2026	Iyer Agencies	South	Technology	TEC-LA-204	52000	7800

Total sales are 3,10,000 rupees and total profit is 38,450 rupees. Use these totals to check your work as you go.

Creating a calculated field

A calculated field is a new field you build from the fields you already have, with a formula. Once created, it sits in the Data pane like any other field, and you can drag it onto shelves.

1. Select Analysis > Create Calculated Field. You can also right-click in the Data pane and choose Create > Calculated Field.
2. In the calculation editor, type a name at the top, for example Profit Ratio.
3. Type the formula in the big box. Tableau suggests field names and functions as you type.
4. Read the message at the bottom. It should say The calculation is valid. If not, select the message to see what is wrong.
5. Select OK. The new field appears in the Data pane with an = sign next to its icon.

A few rules of the formula language:

• Field names go in square brackets: [Sales], [Order Date].
• Text goes in double quotes: "Loss".
• Lines starting with // are comments, notes for humans that Tableau ignores.
• Function names like SUM or DATEDIFF are not case sensitive, but write them in capitals so they stand out.

Here is the process as a chart. Read it from the top, and follow the side arrow when the answer is No.

Profit margin: the right way and the wrong way

Profit margin (profit ratio) is profit divided by sales. There are two ways to write it, and only one is right.

// Profit Ratio (correct)
SUM([Profit]) / SUM([Sales])

This first adds up all profit, then adds up all sales, then divides. That is how an accountant works out margin. To show it as a percentage, right-click Profit Ratio in the Data pane, select Default Properties > Number Format, and choose Percentage.

Put Category on Rows and Profit Ratio on Columns. You should see:

Category	SUM(Sales)	SUM(Profit)	Profit Ratio
Furniture	105000	5800	5.5%
Technology	182000	28550	15.7%
Office Supplies	23000	4100	17.8%

Now the wrong way:

// Profit Ratio (wrong for totals)
[Profit] / [Sales]

This works out a margin for every single row first. When you put it in a chart, Tableau adds those row margins up with SUM. For Technology it adds 20% + 15% + 15% + 15% and shows 65.0%, which is nonsense.

IF and CASE: putting labels on your data

IF

IF checks a condition and returns one result if it is true and another if it is false. ELSEIF adds more conditions, checked from top to bottom. Every IF ends with END.

// Order Status
IF [Profit] < 0 THEN "Loss"
ELSEIF [Profit] / [Sales] < 0.1 THEN "Low margin"
ELSE "Healthy"
END

Put Order ID and Order Status on Rows. Most orders are Healthy, and two stand out:

Order ID	Profit / Sales	Order Status
SO-2005	-10%	Loss
SO-2008	8%	Low margin
All other orders	15% to 20%	Healthy

For a simple two-way test, IIF is shorter. It takes the test, the value if true and the value if false:

// Big Order
IIF([Sales] >= 40000, "Big order", "Normal order")

This marks SO-2001, SO-2004, SO-2007 and SO-2010 as Big order.

CASE

CASE is neater when you compare one field with a list of exact values, like assigning a regional manager. The names here are made up.

// Regional Manager
CASE [Region]
WHEN "North" THEN "Ravi Kumar"
WHEN "South" THEN "Anitha Rao"
WHEN "West"  THEN "Neha Shah"
ELSE "Not assigned"
END

Each order now shows its manager: Kapoor Brothers and Singh Retail orders show Ravi Kumar, and so on. The ELSE line catches any new region you add later.

Date functions

Dates in finance mean due dates, shipping time, months and financial years. Tableau date functions take a date part in single quotes, such as 'day', 'month', 'quarter' or 'year'.

// Days to Ship
DATEDIFF('day', [Order Date], [Ship Date])

DATEDIFF counts the days from the first date to the second. SO-2001 shows 3, SO-2005 shows 7 (the slowest), and five orders show 2.

// Order Month
DATETRUNC('month', [Order Date])

DATETRUNC cuts a date back to the start of its month, so 22 April 2025 becomes 1 April 2025. This is handy for monthly totals.

// Month Name
DATENAME('month', [Order Date])

DATENAME gives the part as text, here “April”, “June” and so on.

The Indian financial year runs from April to March. This formula builds a label like “2025-26” for each order:

// Financial Year
IF MONTH([Order Date]) >= 4
THEN STR(YEAR([Order Date])) + "-" + RIGHT(STR(YEAR([Order Date]) + 1), 2)
ELSE STR(YEAR([Order Date]) - 1) + "-" + RIGHT(STR(YEAR([Order Date])), 2)
END

Line by line: MONTH and YEAR pull out the month and year as numbers. If the month is April (4) or later, the year started this calendar year, otherwise it started last year. STR turns a number into text, and RIGHT keeps the last 2 characters, so 2026 becomes “26”. The + joins pieces of text. Every order in our data shows 2025-26.

Text (string) functions

Text fields often hide useful codes. Our Product Code FUR-CH-101 holds the category, the item type and a number.

// Category Code
LEFT([Product Code], 3)

// Item Type
SPLIT([Product Code], "-", 2)

// Customer and Region
[Customer] + " (" + [Region] + ")"

// Is a Store
CONTAINS([Customer], "Stores")

Formula	Result for SO-2001
LEFT([Product Code], 3)	FUR
SPLIT([Product Code], "-", 2)	CH
[Customer] + " (" + [Region] + ")"	Mehta Stores (West)
CONTAINS([Customer], "Stores")	True
UPPER([Customer])	MEHTA STORES
LEN([Product Code])	10

SPLIT breaks the text at each - and returns piece number 2. CONTAINS returns True or False. Other useful ones are TRIM() to remove extra spaces, REPLACE() to swap text and MID() to take characters from the middle.

Table calculations: running total and percent of total

A table calculation works on the numbers already in your chart, after Tableau has added them up. It can look at other rows of the chart, which normal formulas cannot do. Running totals and shares of the total are the classic examples.

Running total, the quick way

1. Put Order Date on Columns as continuous Month (green pill), and Sales on Rows.
2. Right-click the SUM(Sales) pill and select Quick Table Calculation > Running Total.
3. A small triangle (Δ) appears on the pill. That shows it is now a table calculation.

Month	Sales	Running total
Apr 2025	65000	65000
Jun 2025	10000	75000
Jul 2025	60000	135000
Sep 2025	30000	165000
Oct 2025	8000	173000
Nov 2025	45000	218000
Jan 2026	35000	253000
Feb 2026	5000	258000
Mar 2026	52000	310000

The last value, 3,10,000, matches total sales. That is your check.

You can write the same thing as a formula:

// Running Sales
RUNNING_SUM(SUM([Sales]))

Percent of total

// Share of Sales
SUM([Sales]) / TOTAL(SUM([Sales]))

TOTAL gives the grand total of everything in the chart. Format the field as a percentage. With Category on Rows:

Category	Sales	Share of Sales
Technology	182000	58.7%
Furniture	105000	33.9%
Office Supplies	23000	7.4%

The same result comes from Quick Table Calculation > Percent of Total. For month on month change, try Quick Table Calculation > Percent Difference, or write LOOKUP(SUM([Sales]), -1) to get the previous month’s value.

Level of Detail (LOD) expressions

Every chart has a level of detail: the fields that decide how many marks you see. Put Region on Rows and you get one number per region. But sometimes you need a number at a different level from your chart. For example, a chart by region that shows the average sales per customer, or each order next to that customer’s first purchase date. LOD expressions do this.

They are written inside curly brackets:

{ KEYWORD [Dimension] : AGGREGATE([Measure]) }

There are three keywords:

Keyword	What it does	Example question
FIXED	Works out the number at exactly the dimensions you name, whatever is in the chart	What is each customer’s first purchase date?
INCLUDE	Adds a dimension to the chart’s level, then lets you roll it up	What is the average sales per customer in each region?
EXCLUDE	Removes a dimension from the chart’s level	What share of its region’s sales does each category make?

FIXED: customer first purchase date

// First Purchase Date
{ FIXED [Customer] : MIN([Order Date]) }

For each customer, Tableau finds the earliest order date, and every row of that customer gets the same answer.

Customer	First Purchase Date
Mehta Stores	05-04-2025
Kapoor Brothers	22-04-2025
Iyer Agencies	18-07-2025
Desai Traders	14-10-2025
Singh Retail	08-01-2026

Now you can tell new business from repeat business:

// Order Type
IF [Order Date] = [First Purchase Date] THEN "New customer" ELSE "Repeat" END

Put Order Type on Rows and Sales on Columns. New customer orders bring 1,68,000 rupees (SO-2001, 2002, 2004, 2006 and 2008) and repeat orders bring 1,42,000. That is a question finance teams ask every quarter, answered with two short formulas.

FIXED: average order value per customer

// Customer Avg Order Value
{ FIXED [Customer] : SUM([Sales]) / COUNTD([Order ID]) }

COUNTD counts distinct values, here the number of different orders.

Customer	Total Sales	Orders	Avg Order Value
Iyer Agencies	112000	2	56000
Mehta Stores	95000	3	31667
Kapoor Brothers	60000	3	20000
Singh Retail	35000	1	35000
Desai Traders	8000	1	8000

Mehta Stores’ 95,000 divided by 3 is 31,666.67, shown here rounded.

INCLUDE: average sales per customer by region

Put Region on Rows. If you show AVG([Sales]), you get the average per order. To get the average per customer, include Customer in the calculation:

// Avg Sales per Customer
AVG({ INCLUDE [Customer] : SUM([Sales]) })

Tableau first adds up sales for each customer within each region, then averages those customer totals.

Region	AVG(Sales) per order	Avg Sales per Customer
South	56000	112000
West	25750	51500
North	23750	47500

West has two customers: Mehta Stores (95,000) and Desai Traders (8,000). Their average is 51,500. Check North yourself: Kapoor Brothers 60,000 and Singh Retail 35,000.

EXCLUDE: each category’s share of its region

Put Region and Category on Rows and Sales on Columns. Now you want each category’s sales as a share of its region’s total. First, a field that ignores Category:

// Region Sales
{ EXCLUDE [Category] : SUM([Sales]) }

Then the share:

// Share of Region
SUM([Sales]) / AVG([Region Sales])

We wrap Region Sales in AVG because SUM([Sales]) is already an aggregate and Tableau does not let you mix aggregate and non-aggregate values. Every category mark in a region gets the same region total, so AVG simply returns that total.

Region	Category	Sales	Region Sales	Share of Region
North	Furniture	65000	95000	68.4%
North	Office Supplies	5000	95000	5.3%
North	Technology	25000	95000	26.3%
West	Furniture	40000	103000	38.8%
West	Office Supplies	18000	103000	17.5%
West	Technology	45000	103000	43.7%
South	Technology	112000	112000	100.0%

LOD expressions and filters

This is where many people get confused. Tableau applies filters in a set order, and FIXED runs before normal filters, while INCLUDE and EXCLUDE run after them. Read the chart from top to bottom.

So if you filter Category to Technology only, { FIXED [Customer] : SUM([Sales]) } still shows Mehta Stores at 95,000, its total across all categories, not 45,000. Sometimes that is what you want. If not, right-click the Category filter on the Filters shelf and select Add to Context. The pill turns grey, the filter now runs before FIXED, and Mehta Stores shows 45,000.

Which calculation should you use?

Ask these questions in order. Follow Yes downwards and No to the side.

Common mistakes

• Dividing before adding up. [Profit] / [Sales] summed in a chart gives silly margins like 65%. Use SUM([Profit]) / SUM([Sales]).
• Mixing row-level and aggregate in one formula. [Sales] - SUM([Profit]) gives an error saying you cannot mix aggregate and non-aggregate arguments. Make both sides the same kind: SUM([Sales]) - SUM([Profit]).
• Joining text and numbers with +. "FY " + YEAR([Order Date]) fails, because YEAR returns a number. Wrap it: "FY " + STR(YEAR([Order Date])).
• Forgetting END. Every IF and CASE must close with END.
• Expecting FIXED to follow your filters. It does not, unless the filter is added to context.
• A table calculation computing the wrong way. Check Compute Using whenever a running total or percent looks odd.

Quick recap

• Create fields with Analysis > Create Calculated Field. Fields go in [ ], text in " ", comments after //.
• Row-level formulas work on each row. Aggregate formulas use SUM and friends. For margins, aggregate first.
• IF, IIF and CASE label data. DATEDIFF, DATETRUNC and DATENAME handle dates. LEFT, SPLIT, CONTAINS and + handle text.
• Table calculations like RUNNING_SUM and TOTAL work on the numbers in the chart, in the direction set by Compute Using.
• LOD expressions change the level of a number: FIXED for exact dimensions, INCLUDE to go finer, EXCLUDE to go coarser. FIXED ignores normal filters unless they are in context.

Try it yourself

Use the order table from this lesson.

1. Create Days to Ship and find the average days to ship by Region. (North should show 3.75, because its four orders took 2, 7, 4 and 2 days.)
2. Create a FIXED field for each customer’s Last Purchase Date using MAX, and then a field for the days between first and last purchase.
3. Using INCLUDE, show the average number of orders per customer in each region. Hint: AVG({ INCLUDE [Customer] : COUNTD([Order ID]) }).
4. Add a Share of Sales label to the bar chart you built in the first Tableau lesson.

Where to go next

• Tableau: Build Your First Dashboard if you have not built your first charts yet
• DAX Basics: Measures for Finance to see how Power BI does the same calculations with DAX
• Build a P&L Dashboard in Power BI for a full finance dashboard project
• SQL Joins and GROUP BY with Finance Examples to prepare and summarise data before it reaches Tableau