You are viewing a public preview
Sign in to save your progress, do assignments, join discussions and get feedback.
Calculated fields and business metrics; conditional, string, date and aggregate functions; table calculations such as running total, percent of total, rank, difference and percent difference; Level of Detail expressions: FIXED, INCLUDE and EXCLUDE.
Learning outcomes
The lecture slides for this lesson are below.
By the end of this lesson you can:
Key idea: Where a calculation happens matters as much as the formula. Row-level calculations run on each record, aggregate calculations run on groups, table calculations run on the numbers already in the view, and LOD expressions let you choose the level of detail yourself.
A calculated field is a custom field created by applying formulas or expressions to existing fields. It lets you compute values, combine data or apply conditions that are not directly in the dataset. You create one with Analysis → Create Calculated Field (or right-click in the Data pane); the editor tells you whether "The calculation is valid".
The lecture's example uses two date dimensions, Order Date and Ship Date:
Number of Days = [Ship Date] - [Order Date]
Subtracting two dates returns the number of days between them; DATEDIFF('day', [Order Date], [Ship Date]) gives the same result and lets you choose other units.
Common function groups:
| Group | Examples | Use |
|---|---|---|
| Logical | IF ... THEN ... ELSEIF ... ELSE ... END, CASE, IIF, IFNULL, ZN | Bands, flags, handling NULLs |
| String | LEFT, CONTAINS, UPPER, TRIM, SPLIT, REPLACE | Cleaning and parsing text |
| Date | DATEDIFF, DATEADD, DATETRUNC, DATEPART, YEAR | Durations, periods, cohorts |
| Aggregate | SUM, AVG, MIN, MAX, COUNT, COUNTD | Business metrics |
A row-level calculation is computed for every record, then aggregated in the view: [Sales] - [Profit] gives a cost per order line. An aggregate calculation is computed for each group in the view: SUM([Profit]) / SUM([Sales]) gives the profit ratio of whatever the mark represents (a category, a month). You cannot mix the two in one expression without aggregating everything (Tableau reports "cannot mix aggregate and non-aggregate arguments").
Ratios are the classic trap: AVG([Profit] / [Sales]) averages the ratio of each line, so a tiny order counts as much as a huge one. The business profit ratio is the aggregate form, SUM([Profit]) / SUM([Sales]).
A table calculation is computed on the aggregated values already in the view. Quick Table Calculation offers running total, difference, percent difference, percent of total, rank, percentile, moving average, YTD total, compound growth rate and year-over-year growth. A triangle (delta) on the pill marks a table calculation.
What matters is Compute Using: the direction or dimensions along which the calculation runs (Table across, Table down, Pane, Cell, or specific dimensions). The dimensions it runs along are addressing; the others are partitioning, and the calculation restarts for each partition. In Edit Table Calculation you can choose Restarting every a dimension, for example restart a running total each year.
LOD expressions compute an aggregate at a level of detail you specify, independent of (or relative to) the view:
{FIXED [Customer ID] : MIN([Order Date])}: one value per customer, whatever is in the view (here the first order date).{INCLUDE [Customer ID] : SUM([Sales])}: adds Customer ID to the view's dimensions, then the view aggregates the results, for example AVG of customer sales per Region.{EXCLUDE [Sub-Category] : SUM([Sales])}: removes a dimension from the view's level, for example category totals shown beside sub-category bars.Order of operations matters: FIXED expressions are computed before ordinary dimension filters (only context, data source and extract filters affect them); INCLUDE and EXCLUDE are computed after dimension filters; table calculations come last, on the results in the view.
The customer team asks: "How long do we take to ship, and how many customers buy more than once?" Use the course Orders table.
Days to Ship = DATEDIFF('day', [Order Date], [Ship Date]). Put Ship Mode on Rows and AVG(Days to Ship) on Columns. Each mark averages over the order lines of one ship mode. By definition you expect Same Day near 0; if it is not, the data or the process need checking.IF [Days to Ship] = 0 THEN "Same day"
ELSEIF [Days to Ship] <= 3 THEN "1-3 days"
ELSE "4+ days"
END
Use it on Color to show the mix of delivery speeds per Region.
3. Orders per customer (FIXED LOD). Orders are stored per order line, so counting rows would overcount. Create Orders per Customer = {FIXED [Customer ID] : COUNTD([Order ID])}. This gives one value per customer regardless of the view.
4. Distribution. Convert Orders per Customer to a discrete dimension, put it on Columns and COUNTD(Customer ID) on Rows. The bar chart shows how many customers placed 1, 2, 3 ... orders, which answers "how many buy more than once?".
5. Share of repeat customers (table calculation). Apply Percent of Total to COUNTD(Customer ID), computed across the table, to read the share of customers in each bar.
6. Check filters. If you add a Region filter, the FIXED values ignore it unless you add the filter to context; decide which behaviour the question needs.
SUM([Profit]) / SUM([Sales]), not AVG([Profit] / [Sales]).COUNTD([Order ID]) in a line-level table.Flashcards · Advanced Calculation and Analysis Techniques
10 wordsA custom field created with a formula on existing fields to compute values or apply conditions.
Returns the difference between two dates in the chosen unit.
A calculation computed per group in the view; needed for correct ratios.
A calculation applied to the aggregated values already in the view.
The direction or dimensions along which a table calculation is computed.
A table calculation showing each mark's share of the total in its partition.
Computes an aggregate at the specified dimensions, ignoring the view's dimensions.
Adds dimensions to the view's level before aggregating, then the view re-aggregates.
Removes dimensions from the view's level when computing an aggregate.
Counts distinct values, avoiding overcounts in line-level tables.
SUM([Profit]) / SUM([Sales]). Check: 12.2% overall; Furniture only 0.3%, Office Supplies 20.2%, Technology 17.0%. Why is it wrong to average a row-level ratio instead?Discount Band: IF [Discount] = 0 THEN "No discount" ELSEIF [Discount] <= 0.2 THEN "Up to 20%" ELSE "Over 20%" END.{FIXED [Customer ID] : MIN([Order Date])}, then YEAR of it. Check: customers by first-order year: 2023 = 189, 2024 = 28, 2025 = 9.{FIXED [Order ID] : SUM([Sales])}. Check: 860 orders are worth more than 1,000; the average order value is 1,022.85.{INCLUDE [Customer ID] : SUM([Sales])} and explain how it differs from AVG(Sales).