Calculated Metrics

Calculated metrics combine existing fields into a single aggregated number -- a KPI. They produce values like profit margin, average order value, or conversion rate. The result appears in the VALUES area of a card.

Creating in the Explorer

The Explorer provides a guided builder for creating calculated metrics without writing code.

Steps

Open the Explorer and click + Add Calculated Field
Select Calculated Metric
Enter a name (e.g., "Profit Margin")
Choose an operation (e.g., Ratio)
Select fields for Input A and Input B
Choose aggregation functions for each input
Click Create Metric

Supported Operations

For standard operations, you select two input fields (A and B) and an aggregation function for each. Semaphor builds the expression for you.

Operation	Description	Example
Sum	Add two values	`Revenue + Shipping`
Difference	Subtract one value from another	`Revenue - Cost`
Ratio	Divide one value by another	`Revenue / Orders`
Percent of Total	Calculate percentage contribution	`Region Sales / Total Sales`
Custom	Write your own SQL expression	`SUM({Amount} - {Discount}) / NULLIF(COUNT(DISTINCT {Order ID}), 0)`

Aggregation Functions

For standard operations, choose how each input field is aggregated before the calculation:

SUM, AVG, COUNT, COUNT DISTINCT, MIN, MAX

Example: Average Order Value

Operation: Ratio
Input A: Amount with SUM aggregation
Input B: Order ID with COUNT DISTINCT aggregation
Result: SUM(Amount) / COUNT(DISTINCT Order ID)

Custom Expressions

When none of the standard operations fit your calculation, select Custom to write a SQL expression directly. The UI switches from the two-input builder to an expression editor.

How it works

Set the operation to Custom
The Input A / Input B selectors are replaced by an expression editor
Write your SQL expression using {FieldName} tokens to reference fields
Click fields in the left panel to insert tokens at your cursor position
Click Validate Expression to check for errors
Click Create Metric

Token matching is case-insensitive. {Amount}, {amount}, and {AMOUNT} all resolve to the same field.

Example: Discounted Revenue per Unit

You want SUM(amount - discount) / SUM(quantity) -- a ratio where the numerator does row-level arithmetic before aggregating. This can't be expressed with the standard Ratio operation because Ratio aggregates each input independently.

Expression:

SUM({Amount} - {Discount Amount}) / NULLIF(SUM({Quantity}), 0)

Result: Semaphor generates:

SUM(t0.amount - t0.discount_amount) / NULLIF(SUM(t0.quantity), 0)

Example: Weighted Average Price

You want a quantity-weighted average price -- SUM(price * quantity) / SUM(quantity).

Expression:

SUM({Unit Price} * {Quantity}) / NULLIF(SUM({Quantity}), 0)

When to use Custom vs standard operations

Use standard operations when...	Use Custom when...
The calculation is a simple ratio, sum, or difference of two fields	The expression involves row-level arithmetic before aggregation
Each input needs a single aggregation function	You need multiple aggregate functions in one expression
You don't need conditional logic	You need `CASE WHEN`, `COALESCE`, or other SQL constructs

You can also use Generate with AI to describe your calculation in plain English and have Semaphor generate the expression for you.

Not sure if you need a calculated metric or a calculated dimension? See Choosing the Right Type.

Defining in a Template

For version-controlled definitions, bulk configuration, or CI/CD workflows, define calculated metrics in the calculatedMetrics section of your domain template.

Expression Syntax

Expressions use {input_name} tokens as placeholders for field references. The tokens are replaced with fully-qualified SQL at query time.

calculatedMetrics:
  average_order_value:
    label: "Average Order Value"
    expression: "{total_revenue} / NULLIF({order_count}, 0)"
    inputs:
      total_revenue:
        dataset: orders
        field: amount
        aggregate: SUM
      order_count:
        dataset: orders
        field: order_id
        aggregate: COUNT
    format:
      type: currency
      decimals: 2
      currency: USD

Two Input Types

Column reference -- points to a physical column in a dataset:

inputs:
  total_revenue:
    dataset: orders     # Dataset name
    field: amount       # Column name
    aggregate: SUM      # Aggregation function

Metric reference -- points to another calculated metric (enables nesting):

inputs:
  profit:
    metric: profit      # Name of another calculated metric

Aggregation Rules

There are two places aggregation can be specified, and the rules are:

1. Expression wraps the token in an aggregate function:

# The SUM is in the expression -- no aggregate needed on the input
revenue:
  expression: "SUM({amount})"
  inputs:
    amount: { dataset: orders, field: amount }

2. Expression uses bare tokens:

# The tokens are bare -- each input MUST specify an aggregate
avg_order_value:
  expression: "{total_revenue} / NULLIF({order_count}, 0)"
  inputs:
    total_revenue: { dataset: orders, field: amount, aggregate: SUM }
    order_count: { dataset: orders, field: order_id, aggregate: COUNT }

If a token has no aggregate in either place, validation fails with error E010.

Supported aggregates: SUM, COUNT, AVG, MIN, MAX, MEDIAN, DISTINCT

Example: Building a Custom Metric

Suppose you have an orders dataset with amount, quantity, and discount_amount fields.

Step 1 -- Define a base metric with a custom expression.

Revenue after discounts requires subtracting discount_amount from amount before summing. Place the arithmetic inside SUM() so it runs row-by-row:

calculatedMetrics:
  discounted_revenue:
    label: "Discounted Revenue"
    description: "Total revenue after applying line-item discounts"
    expression: "SUM({amount} - {discount})"
    inputs:
      amount: { dataset: orders, field: amount }
      discount: { dataset: orders, field: discount_amount }
    format: currency

Semaphor generates:

SUM(t0.amount - t0.discount_amount) AS discounted_revenue

Step 2 -- Build a ratio metric on top.

Average revenue per unit references the metric from Step 1 and a column aggregate:

  avg_revenue_per_unit:
    label: "Avg Revenue per Unit"
    description: "Discounted revenue divided by total units sold"
    expression: "{discounted_revenue} / NULLIF({total_units}, 0)"
    inputs:
      discounted_revenue: { metric: discounted_revenue }
      total_units: { dataset: orders, field: quantity, aggregate: SUM }
    format:
      type: currency
      decimals: 2

Semaphor expands the nested reference and generates:

(SUM(t0.amount - t0.discount_amount) / NULLIF(SUM(t0.quantity), 0)) AS avg_revenue_per_unit

Nesting Calculated Metrics

Calculated metrics can reference other calculated metrics. This is how you build layered KPIs:

calculatedMetrics:
  # Layer 1: Base metrics
  revenue:
    label: "Revenue"
    expression: "SUM({amount})"
    inputs:
      amount: { dataset: orders, field: amount }
 
  cost:
    label: "Cost"
    expression: "SUM({cost_amount})"
    inputs:
      cost_amount: { dataset: orders, field: cost }
 
  # Layer 2: Derived from base metrics
  profit:
    label: "Profit"
    expression: "{revenue} - {cost}"
    inputs:
      revenue: { metric: revenue }
      cost: { metric: cost }
 
  # Layer 3: Derived from derived metrics
  profit_margin:
    label: "Profit Margin %"
    expression: "{profit} / NULLIF({revenue}, 0) * 100"
    inputs:
      profit: { metric: profit }
      revenue: { metric: revenue }

At query time, Semaphor recursively expands nested references. The profit_margin metric resolves to:

((SUM(t0.amount) - SUM(t0.cost)) / NULLIF(SUM(t0.amount), 0) * 100)

Maximum nesting depth is 10 levels. Circular references (A references B which references A) are detected and rejected with error E001.

Cross-Dataset Metrics

When inputs reference fields from different datasets, Semaphor automatically generates separate CTEs to aggregate each dataset independently, then joins them. This prevents metric inflation from fan-out joins.

calculatedMetrics:
  net_revenue:
    label: "Net Revenue"
    description: "Revenue minus returns"
    expression: "{total_sales} - COALESCE({total_returns}, 0)"
    inputs:
      total_sales: { dataset: orders, field: amount, aggregate: SUM }
      total_returns: { dataset: returns, field: amount, aggregate: SUM }

When grouped by order_month, Semaphor generates:

WITH orders_agg AS (
  SELECT DATE_TRUNC('month', t0.order_date) AS order_month,
         SUM(t0.amount) AS net_revenue__field_0
  FROM orders AS t0
  GROUP BY DATE_TRUNC('month', t0.order_date)
),
returns_agg AS (
  SELECT DATE_TRUNC('month', t0.return_date) AS order_month,
         SUM(t0.amount) AS net_revenue__field_1
  FROM returns AS t0
  GROUP BY DATE_TRUNC('month', t0.return_date)
)
SELECT COALESCE(orders_agg.order_month, returns_agg.order_month) AS order_month,
       orders_agg.net_revenue__field_0
         - COALESCE(returns_agg.net_revenue__field_1, 0) AS net_revenue
FROM orders_agg
FULL OUTER JOIN returns_agg
  ON orders_agg.order_month = returns_agg.order_month
ORDER BY order_month

Cross-dataset metrics require a grain mapping when grouped by a calculated dimension, so Semaphor knows which column in each dataset maps to the same time grain.

Field Reference

Field	Type	Required	Description
`label`	string	Yes	Display name
`description`	string	No	Help text
`expression`	string	Yes	SQL expression with `{input}` tokens
`inputs`	object	Yes	Map of input name to column or metric reference
`metricType`	enum	No	`base`, `derived`, `calculated`. Defaults to `derived`
`aggregationStrategy`	enum	No	`default`, `symmetric_aggregate`, `aggregate_then_join`, `weighted`
`format`	object	No	Display formatting (see Format Spec below)
`tags`	string[]	No	Categorization tags
`filters`	array	No	Default filters applied to this metric

Format Spec:

format:
  type: currency        # number, currency, percentage, date, string
  decimals: 2           # 0-10
  currency: USD         # For currency type
  prefix: "$"           # Optional prefix
  suffix: ""            # Optional suffix
  thousandsSeparator: true

For a full reference of format properties, see Inline Expressions -- Format Object Reference.

Calculated Metrics

On this page