4.2 · Advanced Calculation and Analysis Techniques
[markdown] content available only on the platform
[markdown] content available only on the platform
Flashcards · Advanced Calculation and Analysis Techniques
Calculated field — A custom field created with a formula on existing fields to compute values or apply conditions.
Number of Days = [Ship Date] - [Order Date]
DATEDIFF — Returns the difference between two dates in the chosen unit.
DATEDIFF('day', [Order Date], [Ship Date])
Aggregate calculation — A calculation computed per group in the view; needed for correct ratios.
SUM([Profit]) / SUM([Sales])
Table calculation — A calculation applied to the aggregated values already in the view.
Running Total of SUM(Sales) by month.
Compute Using — The direction or dimensions along which a table calculation is computed.
Running total restarting every YEAR(Order Date).
Percent of Total — A table calculation showing each mark's share of the total in its partition.
Sales by Region as a share of all sales.
FIXED LOD — Computes an aggregate at the specified dimensions, ignoring the view's dimensions.
{FIXED [Customer ID] : MIN([Order Date])}
INCLUDE LOD — Adds dimensions to the view's level before aggregating, then the view re-aggregates.
{INCLUDE [Customer ID] : SUM([Sales])}
EXCLUDE LOD — Removes dimensions from the view's level when computing an aggregate.
{EXCLUDE [Sub-Category] : SUM([Sales])}
COUNTD — Counts distinct values, avoiding overcounts in line-level tables.
COUNTD([Order ID]) = number of orders.
[pdf] Lecture slides — 4.2 Calculated Fields (PDF) — /api/lesson-media/pdfs/4d3653ff-a3ca-4f63-bf8e-bb93c553e599-1794927037575-7b6880ea4ddcdaaf.pdf
[markdown] content available only on the platform
