Overview

Calculated fields let you create custom KPIs, derived groupings, and column transforms without modifying your underlying data source. They can be defined at multiple levels: on a single card, shared across a dashboard sheet, or in a semantic domain template for organization-wide reuse.

Where a Calculation Goes

In a domain, where a calculation lives depends on one question: does it work row by row, or does it aggregate rows? Semaphor decides this from the expression itself, so you don't set it by hand.

In the domain editor, Add calculation on a dataset follows the same rule: an aggregate becomes a metric, and a row-level expression becomes a fact (Aggregate it) or a dimension (Group or filter by it).

Row-Level Expressions (Facts and Dimensions)

When a calculation transforms each row of one dataset, it's an inline expression on a fact or dimension. It uses the dataset's field names directly, with no inputs mapping:

datasets:
  - name: orders
    fields:
      facts:
        - name: amount_usd
          label: "Amount (USD)"
          dataType: number
          sourceField: amount_cents
          expression: "amount_cents / 100"
          defaultAggregate: SUM
          format: currency
      dimensions:
        - name: order_quarter
          label: "Order Quarter"
          dataType: date
          expression: "DATE_TRUNC('quarter', order_date)"

A fact expression is aggregated by defaultAggregate (or whatever aggregate a card chooses). A dimension expression appears in GROUP BY. Neither may aggregate. A fact expression can use any field of its dataset; a dimension expression can use the dataset's identifiers and dimensions. Either can also use the column named in its own sourceField.

See Inline Expressions for full syntax and examples.

Metrics

A dataset metric aggregates one dataset's rows. It lives in the domain's metrics list and names its dataset:

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

A composed metric combines named inputs. Use it when a calculation spans datasets, or builds on other composed metrics. Each input names the dataset it 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 }

See Calculated Metrics for full syntax, and Calculated Dimensions for domain-level groupings and grain mappings.

Metrics vs Dimensions

There are two roles: metrics (what you measure) and dimensions (what you group by).

Example: If you have 1,000 order rows:

• A dimension like CASE WHEN amount > 100 THEN 'Large' ELSE 'Small' END evaluates each of the 1,000 rows to categorize them
• A metric like SUM(amount) / SUM(cost) first computes SUM(amount) and SUM(cost) across all rows, then divides the two results

Facts sit between the two: a fact is computed per row, like a dimension, but you aggregate it, like a metric.

When You Need Calculated Fields

• Transform raw data: Convert cents to dollars or truncate dates to quarters (fact or dimension expression)
• Standardize business KPIs: Define profit margin, average order value, or conversion rate once and reuse it across all dashboards (dataset metric)
• Create custom groupings: Bucket order amounts into "Small / Medium / Large" or combine text fields (dimension expression)
• Build cross-dataset metrics: Compute net revenue (orders minus returns) by aggregating each dataset independently (composed metric)

Defining in the Domain Template

Define calculated fields in YAML/JSON as part of your domain template. This is the primary way to create domain-level fields for organization-wide reuse.

Row-level expressions go on dataset facts and dimensions:

datasets:
  - name: orders
    fields:
      facts:
        - name: amount_usd
          sourceField: amount_cents
          expression: "amount_cents / 100"
          dataType: number
          defaultAggregate: SUM
          format: currency

Metrics go in the top-level metrics list:

metrics:
  - name: profit_margin
    dataset: orders
    label: "Profit Margin %"
    expression: "(SUM(amount) - SUM(cost)) / NULLIF(SUM(amount), 0) * 100"
    dataType: number

See Calculated Metrics, Calculated Dimensions, and Inline Expressions for template syntax.

Best Practices

Naming:

• Use descriptive names that explain what the field represents
• Include units when relevant (e.g., "Revenue per Order (USD)")
• Follow your organization's naming standards for consistency

Expressions:

• Keep expressions simple: break complex calculations into several metrics that build on each other
• Use NULLIF to prevent division by zero: revenue / NULLIF(order_count, 0)
• Use COALESCE for null handling: COALESCE(discount, 0) * amount
• For complex multi-step calculations, use nested metrics

Choosing where it goes:

• Put row-level work on a fact or dimension, close to the source data
• Put aggregates over one dataset in a dataset metric
• Use a composed metric only when you need inputs from several datasets, or want to build on composed metrics

Documentation:

• Write clear label and description values covering what the field measures, how it's calculated, and any caveats
• Add context with the other names people use and the questions the field answers, so agents pick the right one

Getting Started