Choosing the Right Type

Semaphor offers three ways to define computed fields in a domain template. They look similar but behave differently -- choosing the wrong type leads to unexpected results.

The Key Question: Are You Measuring or Grouping?

The simplest way to decide: if the result is a number you want to measure, it's a metric. If the result is a label you want to group by, it's a dimension.

Consider an orders table with these rows:

Profit Margin → Calculated Metric

Profit margin is SUM(amount - cost) / SUM(amount). You want a single number per group -- the overall margin for East, the overall margin for West.

The calculation happens after rows are grouped: sum up all amounts for East ($700), sum up all profits for East ($250), then divide. This is a calculated metric.

Order Size → Calculated Dimension

Order size categorizes each row as "Small" or "Large" based on the amount. You want a label per row that becomes a new way to group.

The calculation happens before grouping: evaluate each row, assign a label, then group by that label. This is a calculated dimension.

Amount in USD → Inline Expression

If amount is stored in cents and you need dollars, you transform the column before aggregation: amount / 100. The result is still a number you measure, but it's a simple per-row transform on a single column -- no need for a full calculated metric.

At a Glance

Decision Guide

"I want to compute a number"

Use a calculated metric.

Calculated metrics produce aggregated values -- totals, averages, ratios, counts. They appear in the VALUES area of a card.

calculatedMetrics:
  profit_margin:
    label: "Profit Margin %"
    expression: "(SUM({revenue}) - SUM({cost})) / NULLIF(SUM({revenue}), 0) * 100"
    inputs:
      revenue: { dataset: orders, field: amount }
      cost: { dataset: orders, field: cost }
    format:
      type: percent
      decimals: 1

Use a calculated metric when:

• You need a KPI like profit margin, conversion rate, or average order value
• The result should be a single number per group
• You want to combine values from multiple datasets
• You need to nest metrics (profit = revenue - cost, then margin = profit / revenue)

"I want to group by something that doesn't exist as a column"

Use a calculated dimension.

Calculated dimensions produce row-level values that go into GROUP BY. They create categories, buckets, or transformed labels to group your data by.

calculatedDimensions:
  order_size:
    label: "Order Size"
    expression: |
      CASE
        WHEN {amount} > 1000 THEN 'Large'
        WHEN {amount} > 100 THEN 'Medium'
        ELSE 'Small'
      END
    inputs:
      amount: { dataset: orders, field: amount }
    dataType: string

Use a calculated dimension when:

• You need a custom grouping (size buckets, fiscal quarters, region labels)
• You want to truncate dates to a different grain
• You need to combine text fields (full name = first + last)
• The result should appear as a category axis or row header

"I want to transform a column before it's aggregated"

Use an inline expression.

Inline expressions are defined directly on a dataset metric. They transform the raw column value -- like converting units or applying conditional logic -- before the default aggregation (SUM) is applied.

datasets:
  - name: orders
    fields:
      metrics:
        - name: amount_usd
          label: "Amount (USD)"
          dataType: number
          sourceField: amount_cents
          expression: "amount_cents / 100"
          format: currency

Use an inline expression when:

• The transform is simple (unit conversion, rounding, conditional logic)
• It applies to a single column in a single dataset
• You don't need cross-dataset references
• You want to keep the metric close to its source field definition

Common Mistakes

Defining a ratio as a dimension

What you wrote:

calculatedDimensions:
  profit_margin:
    expression: "({amount} - {cost}) / NULLIF({amount}, 0)"
    # ...

What happens: Each row gets its own margin (row 1 = 40%, row 2 = 25%, row 3 = 50%). Semaphor tries to GROUP BY these values, creating a category for every distinct percentage -- not a meaningful KPI.

Fix: This is a number you want to measure, not a label you want to group by. Use a calculated metric:

calculatedMetrics:
  profit_margin:
    expression: "(SUM({amount}) - SUM({cost})) / NULLIF(SUM({amount}), 0) * 100"
    # ...

Defining a category as a metric

What you wrote:

calculatedMetrics:
  order_size:
    expression: "CASE WHEN {amount} > 500 THEN 'Large' ELSE 'Small' END"
    # ...

What happens: Semaphor tries to aggregate the result with SUM, but "Large" and "Small" are strings -- the query fails or produces nonsensical output. Even if it worked, the result can't be used as a grouping axis.

Fix: This is a label you want to group by, not a number you want to measure. Use a calculated dimension:

calculatedDimensions:
  order_size:
    expression: "CASE WHEN {amount} > 500 THEN 'Large' ELSE 'Small' END"
    dataType: string
    # ...

Using a calculated metric for a simple column transform

What you wrote:

calculatedMetrics:
  amount_usd:
    expression: "SUM({amount_cents}) / 100"
    inputs:
      amount_cents: { dataset: orders, field: amount_cents }

What happens: It works, but it's overkill. You've created a domain-level calculated metric for a simple unit conversion that only applies to one column in one dataset.

Fix: Use an inline expression directly on the dataset metric. It's simpler and keeps the definition close to the source:

metrics:
  - name: amount_usd
    sourceField: amount_cents
    expression: "amount_cents / 100"
    format: currency

Summary