Calculated measures & percentagesReference
Some of your most important numbers — profit, margin, cost per unit — aren't a single field. A calculated measure lets you write that formula once, name it, and reuse it anywhere. Add a measure row to your Report_ sheet: its Title is the name, its Measure cell is the formula. Then write the name in any widget's Measure cell:
| Widget | Title | Measure |
|---|---|---|
measure | Profit | sum(total_amount) - sum(cost) |
kpi | Profit | Profit |
The KPI now shows profit across the records on screen (filters included) — and “Profit” is available to every chart, pivot and table on the dashboard.
Two ways to write a formula. You can do the math on the totals, or on every record before totalling — the results can differ, so choose deliberately:
| Style | Example | Use it when |
|---|---|---|
| Over aggregates | sum(amount) - sum(cost) — computed once per group | The math belongs after totalling — ratios, shares, group-level comparisons. |
| Per record | amount - cost — computed on each row, then summarized | The math must happen on each row first — a per-line margin, for example. |
Percentages. To read any value as a share, pick % of total or % of group in the Summarize column. In formulas, pct_of(a, b) is a as a share of b, and variance_pct(a, b) is the change from b to a — both display as percents. A plain ratio like sum(debt) / sum(equity) stays a bare number.
Currency and units follow your fields. You rarely have to say how a measure should be formatted — the display follows the fields in the formula:
| The formula combines | The result shows |
|---|---|
| Money plus or minus money | Money — Profit keeps your currency. |
| Money × or ÷ a plain number (a constant, a count, or a quantity with no unit) | Still money — an average price like sum(line_amount) / sum(quantity) keeps the currency. |
| Money ÷ money, or two different currencies | A plain number. |
pct_of(…) or variance_pct(…) | A percent. |
Overriding the display. When the automatic format isn't what you meant, you have two tools. Wrap a value in scalar(…) to strip its unit on purpose — sum(line_amount) / scalar(sum(quantity)) keeps the currency even when the quantity has units. Or add a Unit column to the measure row to force the format: a currency code (USD), a unit code (KGM), %, or plain.
Translation_ sheet (Object Type measure).