SquareX recipes

Every recipe below is a complete, working set of measures — copy it into the measure editor and swap in your own table and column names. To create a measure: open your model, click ƒx Measures in the toolbar, then New measure. Paste into the Code tab (or build the same shape from a template), watch the diagnostics, and check the compiled-SQL preview before saving.

Recipes assume a small sales model — Sales(OrderId, CustomerId, Amount, Qty, Price, Cost, Region), a Product table, and a date column Date.Date — adjust freely.

Core KPIs

The starter pack

Almost every dashboard starts with these three. Build the small ones first; everything else references them.

Revenue := Sum(Sales.Amount)

Orders := CountDistinct(Sales.OrderId)

AvgOrderValue := Divide([Revenue], [Orders])

Use them on: KPI cards for the headline numbers; any chart’s value well. They aggregate correctly at whatever grain the chart applies — [Revenue] by month, by region, by product all come from this one definition.

Show 0 instead of blank

An aggregate over zero rows is blank (NULL), which renders as an empty cell. When a zero communicates better:

RevenueOrZero := Coalesce([Revenue], 0)

Use it on: tables and matrix visuals where empty groups should read as 0. Keep the blank-returning original for cards where no data and zero revenue mean different things.

Time comparisons

Year-over-year

The standard trio: this year, last year, growth.

Revenue := Sum(Sales.Amount)

RevenueLY := Calculate([Revenue], DateShift(Date.Date, -1, "year"))

YoYGrowthPct := Divide([Revenue] - [RevenueLY], [RevenueLY])

Use them on: a line chart by Date.Date:month with [Revenue] and [RevenueLY] as two series; [YoYGrowthPct] on a KPI card (format as %) or as a tooltip field. The shift is computed on raw dates before bucketing, so week-grain charts stay correct too. Swap -1, "year" for -1, "quarter" or -1, "month" for QoQ/MoM.

Year-to-date pacing

RevenueYTD := YTD([Revenue], Date.Date)

// Last year's accumulation, aligned onto this year's buckets
RevenueYTDLY := Calculate([RevenueYTD], DateShift(Date.Date, -1, "year"))

YTDvsLYPct := Divide([RevenueYTD] - [RevenueYTDLY], [RevenueYTDLY])

Use them on: a line chart by Date.Date:month — the two YTD lines make the classic pacing chart; the gap between them is the story. The chart must group by the measure’s date column (SQX010 tells you if it doesn’t). QTD and MTD are drop-in replacements at quarter/month scope — chart MTD on a day axis.

Trailing 12 months

Smooths seasonality better than any calendar-year cut:

Revenue12mRolling := RollingSum([Revenue], 12, "month")

Use it on: a line chart by Date.Date:month. Months with no sales count as zero — the window never silently shrinks. Filter the chart’s date range freely; the look-back still reaches behind the filter so the first visible points are correct.

Totals & shares

% of total

PctOfTotalRevenue := Divide(
    [Revenue],
    Calculate([Revenue], RemoveFilters(Product))
)

Use it on: a bar chart or table grouped by product — every row divides by the product-spanning total, so the column sums to 100%. List every dimension the chart groups by in RemoveFilters(…); or use RemoveFilters() bare for a denominator that also ignores slicers (the true grand total).

% within group — follows the view

When the chart groups by month and region and you want each region’s share of its month, exclude just the region level; the denominator keeps following every other axis the viewer adds:

MonthTotal := Exclude(Sales.Region, [Revenue])

PctOfMonth := Divide([Revenue], [MonthTotal])

Use it on: a stacked bar by month split by region, or a matrix with months as rows and regions as columns. Unlike the RemoveFilters recipe, nothing is hard-coded to month — regroup by quarter and the subtotal follows.

The benchmark that ignores a slicer

Compare any slice against a fixed reference, no matter what the viewer filters:

// All-regions figure, immune to a Region slicer
RevenueAllRegions := Calculate([Revenue], RemoveFilters(Sales.Region))

// A pinned segment — the override idiom (remove, then add)
WestRevenue := Calculate([Revenue], Sales.Region = "West", RemoveFilters(Sales.Region))

PctVsWest := Divide([Revenue], [WestRevenue])

Use them on: KPI cards side by side (your selection vs all vs West), or as a constant reference series on a line chart. Why the remove-then-add works regardless of written order: removals apply first.

Per-entity analysis

Average revenue per customer

The average of a per-entity total — not the average line amount:

CustomerRevenue := Fixed(Customer.Id, [Revenue])

Use it on: any chart, with the field-well aggregation set to avg — by-region bars give average revenue per customer per region; max gives the biggest customer per region. Prefer Include(Customer.Id, [Revenue]) when the grain should adapt to whatever the viewer groups by rather than being pinned.

Active customers per month

CustomersPerMonth := Fixed(Date.Date:month, CountDistinct(Sales.CustomerId))

Use it on: a line chart by Date.Date:month, or aggregate it (avg) for a typical month KPI card. The inline :month grain replaces the usual date-truncation boilerplate.

Typical vs top-end order value

Means hide skew; medians and percentiles show it:

MedianOrderValue := Median(Sales.Amount)

P90OrderValue := Percentile(Sales.Amount, 0.9)

Use them on: KPI cards next to [AvgOrderValue] — a mean far above the median is itself a finding — or a bar chart by segment for SLA-style reporting (P90 delivery days, P95 response time). Both are dialect-gated: not available on MySQL, SQL Server, or BigQuery sources (the support matrix has each engine’s exact behavior), and neither can be accumulated by YTD/RollingSum.

Line-level math

Weighted totals and weighted averages

When the math must happen per row before summing, use an iterator:

GrossRevenue := SumX(Sales, Sales.Qty * Sales.Price)

WeightedPrice := Divide(SumX(Sales, Sales.Qty * Sales.Price), Sum(Sales.Qty))

Use them on: anywhere a plain Avg(Sales.Price) would mislead — the weighted price weights big orders properly. Inside the row expression, only bare columns of the iterated table are allowed (SQX014 guards the rest).

Lines per order

LinesPerOrder := Divide(CountRows(Sales), CountDistinct(Sales.OrderId))

Use it on: a KPI card or a trend line by month — a rising basket size changes how you read flat order counts.

Balances & snapshots

Inventory on hand, right now

Stock levels, account balances, and headcount are levels, not flows — summing them over time is meaningless. Take the value at the last date that has one:

ClosingStock := LastNonEmpty(Sum(Inventory.OnHand), Inventory.CountedAt)

Use it on: a KPI card (the current level, whatever date it landed on) or grouped by warehouse — each group picks its own last non-empty date. Add a filter with Calculate to ask as of within that filter: the pick moves with it.

Month-end balance trend

EOMStock := PeriodEndSnapshot(Sum(Inventory.OnHand), Inventory.CountedAt, "month")

Use it on: a line chart grouped by Inventory.CountedAt:month — the axis grain must match the snapshot grain (SQX010 enforces it). Each bucket shows the last reading inside it: the classic closing-balance trend.

Labels & tiers

Revenue tiers

Banding on measure values — searched conditions, first true wins:

RevenueTier := Case(
    [Revenue] >= 1000000, "Tier 1",
    [Revenue] >= 100000,  "Tier 2",
    "Tier 3")

Use it on: tables and matrices as a computed label column, or a KPI card’s secondary value. Banding on a column (e.g. Sales.Segment) is row logic and belongs in a calculated field instead — SQX014 will point you there.

A self-describing KPI label

RevenueLabel := "Total: " & ToText(Round([Revenue], 0))

AsOfLabel := "as of " & ToText(Max(Sales.OrderDate))

Use them on: KPI cards and text tiles. & takes strings only — ToText does the conversion explicitly (no silent coercion, SQX006).

Margin % without repeating yourself

MarginPct := Let(rev, [Revenue],
    Divide(rev - Sum(Sales.Cost), rev))

Use it on: KPI cards and by-product bars (format as %). Let computes the bound expression once in the compiled SQL — cleaner to read, cheaper to run. Let measures are code-lane only; the template builder shows them read-only.

The SquareX guide — the concepts behind every recipe.
Worked examples — the engine’s own acceptance corpus.
Function reference — each function’s when to use it and under the hood.
Filter context & evaluation — why these compose the way they do.