Overview

Calculated fields let you create custom KPIs, derived groupings, and column transforms without modifying your underlying data source. They work at every level -- on a single card, across a dashboard, or saved to a semantic domain for organization-wide reuse.

There are three types of calculated fields, each serving a different purpose:

Example: If you have 1,000 order rows:

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

When You Need Calculated Fields

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

Two Ways to Create Calculated Fields

In the Domain Template

Define calculated fields in YAML/JSON as part of your domain template. This is ideal for standardized, version-controlled business logic.

calculatedMetrics:
  profit_margin:
    label: "Profit Margin %"
    expression: "{profit} / NULLIF({revenue}, 0) * 100"
    inputs:
      profit: { metric: profit }
      revenue: { metric: revenue }

See Calculated Metrics and Calculated Dimensions for template syntax.

In the Explorer UI

Create calculated fields interactively without editing templates:

1. Open the Explorer and click + Add Calculated Field
2. Select Calculated Metric or Calculated Dimension
3. Configure the expression and inputs
4. Click Create

Fields created in the Explorer can be scoped to a card, dashboard, or saved to the domain (see Scope Options below).

AI-Powered Expression Generation

When creating calculated dimensions in the Explorer, you can describe what you want in plain English and Semaphor generates the SQL expression for you.

1. Click Generate with AI in the expression editor
2. Describe your calculation in natural language
3. Review the generated expression
4. Click Use Expression to apply it, or Regenerate for a different approach

Example prompts:

• "Create buckets for order amount: small under 100, medium 100-500, large over 500"
• "Extract the quarter from the order date"
• "Calculate profit as revenue minus cost"

Expression Validation

Semaphor validates expressions to catch errors before you save.

What gets validated:

• Field references -- All {FieldName} tokens must match available fields
• SQL syntax -- Expression must be valid SQL for your database dialect
• Self-references -- Cannot reference the field being edited (prevents circular references)
• Function support -- Functions must be supported by your database

After clicking Validate Expression, you'll see:

• Tokens found -- Which fields were detected and resolved
• Errors -- Issues that must be fixed (unknown fields, syntax errors)
• Warnings -- Suggestions for improvement (missing ELSE clause, division by zero risk)
• Inferred output type -- The detected data type of the result

Scope Options

Control where your calculated field is available.

Permissions by User Type

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 multiple calculated fields
• Use NULLIF to prevent division by zero: {Revenue} / NULLIF({Orders}, 0)
• Use COALESCE for null handling: COALESCE({Discount}, 0) * {Amount}
• For complex multi-step calculations, use nested metrics

Documentation:

• When saving to domain scope, write clear descriptions covering what the field measures, how it's calculated, and any caveats

Getting Started