Calculated Fields

Create custom metrics and dimensions in the Explorer without modifying your data source

Calculated fields let you create custom KPIs, derived groupings, and column transforms directly in the Explorer -- without modifying your underlying data source or editing domain templates. Fields created in the Explorer can be scoped to a single card or shared across all cards on the current sheet.

Domain-Level Calculated Fields

For organization-wide reuse, administrators define calculated fields in the semantic domain template. Domain-level fields support inline expressions (single-entity), root calculated metrics (multi-entity), and root calculated dimensions (multi-entity). They appear with a Domain badge in the field list and are read-only for dashboard users.

Creating in the Explorer

Open the Explorer and click + Add Calculated Field
Select Calculated Metric or Calculated Dimension
Configure the expression and inputs
Click Create

Calculated Metrics

Calculated metrics produce aggregated numbers (KPIs) like profit margin, average order value, or conversion rate. Choose an operation or write a custom expression:

Operation	Description	Example
Sum	`A + B`	Total revenue + shipping
Difference	`A - B`	Revenue - cost
Ratio	`A / B`	Revenue / orders
Percent of Total	`A / SUM(A)`	Category share
Custom	Any SQL expression	Complex formulas

Calculated Dimensions

Calculated dimensions produce row-level grouping values. Write a SQL expression using {FieldName} tokens:

CASE
  WHEN {Amount} > 1000 THEN 'Large'
  WHEN {Amount} > 100 THEN 'Medium'
  ELSE 'Small'
END

AI-Powered Expression Generation

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

Click Generate with AI in the expression editor
Describe your calculation in natural language
Review the generated expression
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"

Always review AI-generated expressions before using them. The AI provides a starting point that you can modify as needed.

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

Error	Cause	Solution
Unknown field token	Field name doesn't match any available field	Check spelling or use the field picker
Self-reference detected	Expression references the field being edited	Remove the circular reference
Unsupported function	Database doesn't support this SQL function	Use an alternative function for your dialect

Scope Options

Control where your calculated field is available.

Scope	Available To	Use Case
Card (default)	All users viewing the card	Experimental calculations, one-time analysis
Dashboard	All users viewing the current sheet	Fields shared across multiple charts on the same sheet

Permissions by User Type

Action	Organization User	Tenant User
View and use calculated fields	Yes	Yes
Create card-scoped fields	Yes	Yes
Create dashboard-scoped fields	Yes	Yes
Edit/delete card-scoped fields	Yes	Yes
Edit/delete dashboard-scoped fields	Yes	Yes
Define domain-level fields (via template)	Admin only	No

Organization users are employees with direct access to Semaphor. Tenant users are end users from your customers who access embedded dashboards. Domain-level fields are read-only for all dashboard users, ensuring consistent business logic across all customer-facing analytics.

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}