# Metrics

> Define a number once, as SQL over one table, and every query, report and AI assistant uses that definition. Build metrics out of other metrics, across tables too.

A [metric](https://docs.sanda-os.com.au/reference/glossary#metric) is a number you have defined once. `net revenue` is gross revenue less discounts, for paid invoices, in Australian dollars. Say that here, and every query, report, cherry answer and AI assistant uses it instead of working out its own version.

A metric is written the way you would write SQL for one table, with bare column names and no schema prefix. It is measured on one table, the one you pick when you define it.

| Field | Example | What it is for |
|---|---|---|
| Name | `net revenue` | What people call the number. It is also how other metrics refer to it |
| Expression | `{gross revenue} - sum(discount_amount)` | The definition: one SQL fragment over the table's columns |
| Only counting rows where | `status = 'PAID'` | A condition that belongs to this number |
| Table | `invoices` | The table the number is measured on |
| Unit | `AUD` | Shown beside the number |
| Also called | `net sales` | Other words for it |
| Notes | `Paid invoices, after discounts.` | What it is for and what it excludes |

Open the panel from the rail: press **metrics** on the map. The panel lists every metric with its expression and the table it is measured on.

## Define a metric

There are two routes, both in the metrics panel.

### Write one

:::steps
1. **Press write one.** A form opens at the top of the panel. You need at least one table on the map.
2. **Name it.** Type the name, for example `total revenue`. Capitals are lowercased when you save. See [naming rules](https://docs.sanda-os.com.au/semantic-fluid/naming).
3. **Write the expression.** For example `sum(gross_amount)`. Under the box, **Insert** chips add a reference to an existing metric in one press. Once a table is picked, the chips offer only metrics measured on that table. Before one is picked, pressing a chip also picks its table.
4. **Add a condition if the number needs one.** In the field that reads "only counting rows where", type `status = 'PAID'`.
5. **Pick the table.** The **which table is it measured on?** list is required. An expression such as `sum(amount)` means a different number on every table that has an `amount`, so sanda will not guess.
6. **Add a unit, other words and notes if you want them.** Then press **save metric**.
:::

### Describe it

If you would rather say what you want, press **describe a metric**, type a sentence such as "refund rate by month" or "the share of orders that came back", and press **draft it**. If the map has categories, chips under **Group by** (up to six) add "by region" and the like to your sentence.

sanda drafts the metric from your own tables and columns, in an editable form headed "sanda's draft · check it". It lists the columns the draft reads and how confident it is. Check the SQL, correct it, then choose:

- **save metric** puts it live in your metrics.
- **save for review** parks it in the [review queue](https://docs.sanda-os.com.au/semantic-fluid/review) instead, for a second pair of eyes.

If your data cannot support what you described, sanda says exactly what is missing (a column, a table, a join) instead of inventing something. The draft is an AI call, and it is billed like any other. See [Learn from my data](https://docs.sanda-os.com.au/semantic-fluid/learn#cost-and-time).

## Write the expression

- **One SQL fragment.** An aggregate, or arithmetic over aggregates. No semicolon, and no comment (`--` or `/* */`). It can be up to 2,000 characters. A fragment that cannot contain those cannot end the statement it is placed in.
- **Columns of one table.** Use bare names such as `unit_price`, never `mapping.products.unit_price`.
- **Constants are safe.** Text inside quotes, such as `'PAID'`, is left alone.

```sql title="Examples"
sum(unit_price * quantity)
count(distinct customer_id)
avg(total)
count(*) filter (where status = 'refunded')::numeric / nullif(count(*), 0)
```

When several tables are in one question, sanda writes each column out in full before it runs the query, so two tables that both have a `unit_price` never make it ambiguous. If a word in a definition is a column of another table in the same question, sanda stops and says which definition, which word and which two tables, and suggests the fix: write the column with its table name, or measure the metric on the other table.

## Build on other metrics

Refer to another metric by putting its name in braces. `net revenue` can be `{gross revenue} - sum(discount_amount)`. Before anything is run, sanda expands the reference, so `net revenue` arrives as `(sum(total)) - sum(discount_amount)`. You write `gross revenue` once and reuse it everywhere.

- Names match without regard to case, and spaces around the name are ignored: `{ Net Revenue }` and `{net revenue}` are the same reference.
- A metric cannot be built out of itself, directly or through another metric. sanda detects the loop, leaves the reference unexpanded, and tells the agent not to use the metric. sanda also refuses to run a metric whose reference names something that is not defined.
- A referenced metric keeps its own condition. `paid share` can be `{net revenue} / nullif({gross revenue}, 0)`: the top half counts only paid invoices, as `net revenue` does, and the bottom half counts them all.
- A metric can be built from metrics on other tables, as long as a [relationship](https://docs.sanda-os.com.au/semantic-fluid/relationships) joins them. sanda works out each part on its own table and then does the arithmetic: with `revenue` summed over the order lines and `order count` counted over the orders, `average order value` is `{revenue} / nullif({order count}, 0)`, and each order is counted once however many lines it has. Without a relationship between the tables, the [workbench](https://docs.sanda-os.com.au/semantic-fluid/query-the-fluid) and `run_query` refuse it and say which join to draw.
- In a metric that is worked out table by table, keep every column inside an aggregate. Arithmetic around the references, such as `nullif(..., 0)` or `::numeric`, is fine. A bare column outside `sum()` or `count()` belongs to no one table's part, so sanda refuses it and names the metric.

To rename a metric, open it, press **edit**, change the name and save. sanda repoints every metric that refers to the old name, and tells you which ones before you save. Deleting a metric that others refer to leaves those metrics unresolved.

## Filters on a metric

The field "only counting rows where" is that number's own condition. `net revenue` above counts only paid invoices.

It belongs to that one number. When it is the only number in a question, or every number shares it, the condition becomes the query's `where`. Beside numbers that do not share it, sanda writes it onto that number alone, as `filter (where status = 'PAID')`, so `net revenue` and `invoice count` side by side give paid revenue and every invoice. Two metrics whose conditions disagree, such as paid and draft revenue, sit side by side the same way.

When several metrics, or many questions, need the same condition, name it once as a [filter](https://docs.sanda-os.com.au/semantic-fluid/dimensions-and-filters#named-filters) and use it by name.

## Unit, period and display

- **Unit** is text such as `AUD`. It shows beside the metric on cards and in what the agent is told.
- **Also called** lists other words for the number. A word that names exactly one metric finds it. If two metrics both answer to `revenue`, that word is ambiguous and finds neither.

The list view's **metrics** tab has three more fields:

- **Reported per** is the natural period, such as `month`. The agent is told "per month".
- **Shape** records how the number is built: sum, average, count, distinct count, minimum, maximum, ratio or something else.
- **Shown as** records how it should read: plain number, currency, percent, whole number, decimal or duration.

The agent's text uses the definition, the unit and the period. Shape and display are kept with the metric and included in the JSON download of what the agent is told.

On a report, **Shown as** and **Unit** decide how the metric's figures read on kpi, bar and line blocks, on the canvas, a shared link, the reports portal and the email alike. See [blocks](https://docs.sanda-os.com.au/reports/blocks).

## What sanda does with a metric

Ask for `net revenue` by `region` and sanda writes this, from your definitions, with no AI involved:

```sql title="What sanda composes"
select (sum("mapping"."billing__invoices"."total")) - sum("mapping"."billing__invoices"."discount_amount") as "net_revenue",
       "mapping"."billing__customers"."region" as "region"
from "mapping"."billing__invoices"
left join "mapping"."billing__customers"
  on "mapping"."billing__customers"."customer_id" = "mapping"."billing__invoices"."customer_id"
where ("mapping"."billing__invoices"."status" = 'PAID')
group by 2
order by 1 desc
limit 16
```

The reference was expanded, the condition became a `where`, the join followed a relationship you signed off, and the number is grouped by region, largest first.

The join matters. Reaching from many invoices to one customer is a lookup, so every invoice keeps exactly one customer and the total is the same total. It is a `left join`, so an invoice whose customer is missing is kept, in a group with no region, rather than dropped from the total.

The other direction is different, because one customer's columns repeat once per invoice. When a question puts a number measured on customers beside the invoices, sanda counts each customer once by the table's key columns, in a part of the query of its own, and lines the two up by region. When a question would need two tables that each have many rows per customer joined to each other, such as invoices and payments, sanda declines and says why rather than return a number that is too large:

> sanda won’t join mapping.billing__payments to mapping.billing__customers here: one mapping.billing__customers row matches many mapping.billing__payments rows, so every total on the one-row side would be counted once per match and come back too large. Build this from the mapping.billing__payments side instead, or ask for the two tables separately.

A refusal is an answer about the shape of the question. See [relationships](https://docs.sanda-os.com.au/semantic-fluid/relationships#why-the-shape-matters).

## More examples

| Name | Table | Expression | Condition |
|---|---|---|---|
| `gross revenue` | `invoices` | `sum(total)` | |
| `net revenue` | `invoices` | `{gross revenue} - sum(discount_amount)` | `status = 'PAID'` |
| `invoice count` | `invoices` | `count(*)` | |
| `average invoice value` | `invoices` | `avg(total)` | |
| `refund rate` | `orders` | `count(*) filter (where status = 'refunded')::numeric / nullif(count(*), 0)` | |
| `overdue invoices` | `invoices` | `count(*)` | `status <> 'PAID' and due_at < now()` |

## Edit or delete

Press a metric in the panel to open it. **edit** opens the form on its current values, and **save changes** replaces the definition: every report and chat answer picks it up on its next run. **Reported per**, **Shape** and **Shown as**, which the panel's form does not show, keep their values, and so do the metrics repointed by a rename. The bin deletes the metric.

cherry can define metrics for you too: "define net revenue as gross revenue less discounts, for paid invoices". In Autonomous mode a metric cherry defines is live at once, and in Ask first mode it waits for you to confirm. See [actions and approvals](https://docs.sanda-os.com.au/cherry/actions-and-approvals).

:::links
- [Dimensions and filters](https://docs.sanda-os.com.au/semantic-fluid/dimensions-and-filters): The slicing and the conditions to use with a metric.
- [Relationships](https://docs.sanda-os.com.au/semantic-fluid/relationships): How a metric reaches other tables' columns.
- [Query the fluid](https://docs.sanda-os.com.au/semantic-fluid/query-the-fluid): Try a metric against a dimension.
:::