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 calculations are row-level fact and dimension expressions on a dataset, dataset metrics that aggregate one dataset, and composed metrics that combine several datasets. 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
- Choose when it runs: Per row (a column you can group, filter, or aggregate) or Aggregate (a metric such as a total, ratio, or rate)
- 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'
ENDAI-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 |
Where calculated fields live
A calculated field lives in one of two places:
| Place | Available to | Use case |
|---|---|---|
| Domain | Every dashboard built on the semantic domain | Shared business logic, defined once by an admin |
| Dashboard | Every card on the dashboard | A formula one dashboard needs, over one dataset's fields |
A new field you create in the editor goes to the dashboard. Matrix cards keep their own calculations, built in the Matrix editor.
Saving a dashboard through the API
When you save a dashboard template through the API, each calculated column a card shows must point at its definition:
- A domain calculation: the column carries
fieldScope: "domain"and the domain calculation'scalculatedFieldOrigin. - A dashboard field: the column carries
fieldScope: "dashboard"and acalculatedFieldOriginwhoseidis a field in the template's top-levelcalculatedFields.
A save is refused with calculated_column_without_definition when a card shows a column that copies a formula without pointing at a definition, or when a card keeps its own calculatedFields. The message names the card and the column, and details.errors lists each one. Matrix cards and typed derived columns (columns that carry their own derivedField definition) are not checked.
Permissions by User Type
| Action | Organization User | Tenant User |
|---|---|---|
| View and use calculated fields | Yes | Yes |
| Create dashboard-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
NULLIFto prevent division by zero:{Revenue} / NULLIF({Orders}, 0) - Use
COALESCEfor null handling:COALESCE({Discount}, 0) * {Amount}