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.

In a domain, every metric lives in one top-level metrics list, in one of two forms:

• A dataset metric aggregates one dataset's rows. It names that dataset with dataset and uses its field names directly: SUM(amount) / NULLIF(COUNT(DISTINCT order_id), 0).
• A composed metric combines named inputs with {token} syntax. Use it when a calculation spans datasets, or builds on other composed metrics.

Creating in the Explorer

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

Steps

1. Open the Explorer and click + Add Calculated Field
2. Choose the Aggregate type
3. Enter a name (e.g., "Profit Margin")
4. Choose an operation (e.g., Ratio)
5. Select fields for Input A and Input B
6. Choose aggregation functions for each input
7. 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.

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

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

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

Defining in a Template

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

Dataset Metrics

A dataset metric is an aggregate over one dataset's rows. dataset names that dataset, and the expression uses its field names (and its other dataset metrics) directly:

metrics:
  - name: average_order_value
    dataset: orders
    label: "Average Order Value"
    expression: "SUM(amount) / NULLIF(COUNT(DISTINCT order_id), 0)"
    dataType: number
    format:
      type: currency
      decimals: 2
      currency: USD

The expression must aggregate. A row-level expression is refused (row_expression_in_dataset_metric): add it as a fact instead. A reference to another dataset's field is refused (dataset_metric_references_other_dataset): use a composed metric.

You can write field names plain (amount) or in braces ({amount}). Two datasets can each have a metric with the same name; a metric is identified by its dataset and name.

Composed Metrics

A composed metric uses {input_name} tokens as placeholders. Each token maps to an input in inputs, which names where its value comes from:

metrics:
  - name: net_revenue
    label: "Net Revenue"
    expression: "{total_sales} - COALESCE({total_returns}, 0)"
    inputs:
      total_sales:
        dataset: orders
        field: amount
        aggregate: SUM
      total_returns:
        dataset: returns
        field: amount
        aggregate: SUM
    format:
      type: currency
      decimals: 2
      currency: USD

Three Input Types

Field reference points to a field (identifier, fact, or dimension) of a dataset:

inputs:
  total_sales:
    dataset: orders     # Dataset name
    field: amount       # Field name
    aggregate: SUM      # Aggregation function

Dataset metric reference points to a metric of a dataset:

inputs:
  revenue:
    metric: revenue     # Name of the dataset metric
    dataset: orders     # Its dataset

Composed metric reference points to another composed metric (enables nesting):

inputs:
  net_revenue:
    metric: net_revenue # Name of another composed metric (no dataset)

Aggregation Rules

A composed metric's aggregation comes from one of these places:

1. Expression wraps the token in an aggregate function:

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

2. Expression uses bare tokens:

# The tokens are bare; each field input MUST specify an aggregate
- name: sales_per_return
  expression: "{total_sales} / NULLIF({return_count}, 0)"
  inputs:
    total_sales: { dataset: orders, field: amount, aggregate: SUM }
    return_count: { dataset: returns, field: return_id, aggregate: COUNT }

3. Metric inputs bring their own aggregation:

- name: net_revenue
  expression: "{revenue} - COALESCE({returned}, 0)"
  inputs:
    revenue: { metric: revenue, dataset: orders }
    returned: { dataset: returns, field: amount, aggregate: SUM }

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 facts.

Step 1: Define a dataset metric with a custom expression.

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

metrics:
  - name: discounted_revenue
    dataset: orders
    label: "Discounted Revenue"
    description: "Total revenue after applying line-item discounts"
    expression: "SUM(amount - discount_amount)"
    dataType: number
    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 by name:

  - name: avg_revenue_per_unit
    dataset: orders
    label: "Avg Revenue per Unit"
    description: "Discounted revenue divided by total units sold"
    expression: "discounted_revenue / NULLIF(SUM(quantity), 0)"
    dataType: number
    format:
      type: currency
      decimals: 2

Semaphor expands the reference and generates:

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

Nesting Metrics

Metrics can build on other metrics. Within one dataset, dataset metrics reference each other by name:

metrics:
  # Layer 1: Base metrics
  - name: revenue
    dataset: orders
    label: "Revenue"
    expression: "SUM(amount)"
    dataType: number

  - name: total_cost
    dataset: orders
    label: "Cost"
    expression: "SUM(cost)"
    dataType: number

  # Layer 2: Derived from base metrics
  - name: profit
    dataset: orders
    label: "Profit"
    expression: "revenue - total_cost"
    dataType: number

  # Layer 3: Derived from derived metrics
  - name: profit_margin
    dataset: orders
    label: "Profit Margin %"
    expression: "profit / NULLIF(revenue, 0) * 100"
    dataType: number

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)

Composed metrics nest the same way through metric inputs: { metric: profit, dataset: orders } for a dataset metric, or { metric: net_revenue } for another composed metric.

Cross-Dataset Metrics

When a composed metric's inputs come from different datasets, Semaphor generates separate CTEs to aggregate each dataset independently, then joins them. This prevents metric inflation from fan-out joins.

metrics:
  - name: 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

Field Reference

Both forms:

Dataset metric only:

Composed metric only:

A metric with both dataset and inputs, or neither, is refused.

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.