Embiro BI

Documentation › Datasets

Metrics and calculated columns

Define reusable metrics such as revenue or average order value, and calculated columns such as month or profit, on a dataset.

Metrics and calculated columns let you define a business calculation once, on the dataset, and reuse it in every chart. Both are written as SQL expressions in your database's own SQL dialect.

Metrics

A metric is an aggregate: it turns many rows into one number per group.

  1. Edit the dataset (Datasets → pencil icon) and open Metrics.
  2. Click + Add item.
  3. Fill in:
    • Metric key: a short name without spaces, such as revenue;
    • Label: what charts show, such as Revenue (UGX);
    • SQL expression: the calculation, such as SUM(revenue_ugx);
    • optionally a D3 format such as ,.0f and a Description.
  4. Click Save.

The Metrics tab of Edit Dataset, listing metrics such as revenue, orders, average order value and units sold with their SQL expressions

Useful metric expressions (PostgreSQL):

Metric SQL expression
Revenue SUM(revenue_ugx)
Orders COUNT(DISTINCT order_id)
Average order value SUM(revenue_ugx) / NULLIF(COUNT(DISTINCT order_id), 0)
Gross margin % 100.0 * SUM(revenue_ugx - cost_ugx) / NULLIF(SUM(revenue_ugx), 0)
Customers COUNT(DISTINCT customer_id)
Revenue from mobile money SUM(CASE WHEN payment_method = 'Mobile Money' THEN revenue_ugx ELSE 0 END)

Use NULLIF(..., 0) in divisions so a group with nothing in it shows empty instead of an error.

Ad-hoc metrics

You don't have to save every metric on the dataset. In the chart editor, Metrics → + lets you pick a column and an aggregate (Simple) or type an expression (Custom SQL) for that chart only. Save a metric on the dataset once two charts need it.

Calculated columns

A calculated column works on each row. Use it like an ordinary column: to group by, filter on, or put on the X-axis.

  1. Edit the dataset and open Calculated columns.
  2. Click + Add item.
  3. Give it a Column name, such as profit_ugx, and the SQL expression, such as revenue_ugx - cost_ugx.
  4. Tick Is temporal if it is a date, Is filterable and Is dimension as needed.
  5. Click Save.

Examples (PostgreSQL):

Column SQL expression
profit_ugx revenue_ugx - cost_ugx
order_month (temporal) DATE_TRUNC('month', order_date)
size_band CASE WHEN revenue_ugx >= 1000000 THEN 'Large' WHEN revenue_ugx >= 200000 THEN 'Medium' ELSE 'Small' END
full_name first_name \|\| ' ' \|\| last_name
order_date from text (temporal) TO_DATE(order_date_text, 'DD/MM/YYYY')

For MySQL use its functions instead, such as DATE_FORMAT(order_date, '%Y-%m-01') or CONCAT(first_name, ' ', last_name). For Google Sheets and Jotform datasets, expressions use SQLite functions, such as CAST(amount AS REAL) or strftime('%Y-%m', created_at).

Metrics from dbt

If your organization uses dbt, metrics defined in your dbt project can be synced onto the matching datasets automatically. See dbt integration.

Something unclear or missing? Tell us and we'll improve this page.