Creating a Calculated Metric

A calculated metric is a metric built from other metrics using a formula - for example, Net income = Revenue − Expenses, or Gross Margin % = (Revenue − COGS) ÷ Revenue. Once defined, the calculated metric becomes a single source of truth that you and your team can use anywhere in the Semantic Layer.

This article walks through creating a calculated metric from start to finish. For metrics built directly from a source table, see Creating a base metric.

Before you start 

To create a calculated metric, you need:

  • Permission to manage metrics. The minimum role required to create and edit metrics is Data Manager. Roles are assigned by your Datarails admin.
  • The metrics you want to reference must already exist in the Semantic Layer. Create any missing metrics first.
  • A clear definition of the calculation, including the formula and the format the result should be displayed in.

Open the new metric editor 

  1. From the left navigation, open the Setup section and select Metrics & dimensions. Make sure the Metrics tab is selected.
  2. Click + New metric. The New metric editor opens with the Calculated metric tab selected by default (“Created from other metrics”).

 

Build the formula 

The Formula field is where you define how the calculated metric is built from existing metrics. The Semantic Layer supports the arithmetic operators +, −, ×, and ÷.

Add the first metric

  1. Click inside the Formula field. A METRICS list appears showing all the metrics available to you, with an icon indicating each one’s format (for example, $ for currency, % for percentage).
  2. Pick a metric from the list, or start typing to filter the list to matching metric names.
  3. The selected metric is inserted as a chip in the formula. By default, the chip references the metric for the Current period - meaning the period the user is currently viewing.

 

Add an operator

After a metric chip, choose an operator to combine it with the next metric.

  1. Pick an operator from the suggestion menu (+, −, ×, ÷), or type the operator directly.
  2. The metric picker reappears so you can add the next metric.

 

Set the time context for each metric

Every metric chip in the formula has its own time context - the period from which that metric’s value is read. By default, every chip uses Current period, meaning the metric is read from the same period the user is viewing. For straightforward calculations like Net income = Revenue − Expenses, the default is exactly what you want.

For finance KPIs that compare across time, look at a specific moment, or use a days-based denominator, you can change a chip’s time context.

Reference a metric at a different point in time

  1. Click the time selector on the metric chip (the part of the chip that reads Current period).
  2. The time context picker opens, with three options:
    • Current period - the default. The metric is read from the same period the user is viewing.
    • N periods ago (under Relative period) - enter a number and pick a unit: year, quarter, month, day, or Period. The metric is read from that many periods before the one the user is viewing. For example, 1 year ago for a year-over-year comparison, or 3 months ago for a lagged calculation.
    • End of last (under Point in time) - pick a unit (year, quarter, or month). The metric is read from its closing value at the end of the prior year, quarter, or month.
  3. The chip updates to show the new time context (for example, 1 year ago or End of last year). The Preview recalculates instantly.

 

The Period unit is dynamic: instead of a fixed calendar step, it follows the time period the report is viewed at. 1 Period ago reads one month back in a monthly view, one quarter back in a quarterly view, and one year back in an annual view - so a single definition gives the right period-over-period comparison at any grain. The fixed units (year, quarter, month, day) always step by that exact unit regardless of how the report is viewed. On the chip, the dynamic unit appears without a named unit (1 Period ago, 3 Periods ago), while fixed units name the unit (3 Months ago).

The End of last option is available only for metrics whose time aggregation is End of period - for example, Cash, AR, MRR, or Headcount. For Sum or Average metrics, this option is shown but disabled.

Cumulative metrics in formulas: Cumulative Sum and Cumulative Count metrics work the same as any other metric when used as operands (the metrics and constants in the formula). The time modifiers above return the cumulative value at the requested point in time - for example, N periods ago returns the cumulative value at the end of that prior period.

Days in period

Days in period is a built-in value equal to the number of calendar days in the period the user is currently viewing - for example, 31 for January, 90 or 91 for Q1, and 365 or 366 for a full year. You can use it in any formula like a constant.

Days in period is the foundation of days-based KPIs. For example:

  • DSO (Days Sales Outstanding) = (Accounts Receivable ÷ Revenue) × Days in period
  • DPO (Days Payable Outstanding) = (Accounts Payable ÷ COGS) × Days in period

Periods in year

Periods in year is a built-in value that scales a period’s result up to a full-year equivalent, based on the time period the report is viewed at - 12 in a monthly view, 4 in a quarterly view, 1 in an annual view, and the matching factor for to-date windows (for example, 3 for a year-to-date view through the fourth month). Like Days in period, you use it in a formula like a constant, and it can’t take a time modifier.

Use it to annualize a run-rate metric from a single definition - for example, an annualized recurring-revenue metric that returns the right number whether the viewer is looking monthly, quarterly, or annually.

A metric that uses Periods in year can’t be viewed at day grain. Day-level requests return a clear error rather than a value.

Finance KPI examples

Common KPIs you can build with time-aware formulas:

  • Year-over-year revenue change - Revenue (current period) − Revenue (1 year ago).
  • YTD cash change - Cash − Cash (year end of the prior year).
  • DSO - (AR ÷ Revenue) × Days in period.
  • Lagged CAC - New customers ÷ Marketing expense (3 months ago), accounting for the lag between marketing investment and customer conversion.

Formula validation

The Formula field validates as you build. When the formula is incomplete or invalid - for example, two metrics with no operator between them - the field shows a red border and the message:

“Invalid formula. Please check your syntax and try again.”
The Save button stays disabled until the formula is valid.

When the formula is valid, the Preview on the right populates with a live result so you can sanity-check the values before saving. By default it shows Actuals only - toggle Show Actuals only off if you want to see all scenarios.

How the formula aggregates over time

A calculated metric doesn’t have its own time aggregation setting. How the result rolls up across time periods is determined by the operands and operators in the formula:

  • All operands have the same time aggregation - the result follows that same aggregation. For example, Revenue − Expenses (both Sum metrics) accumulates across periods like a regular Sum metric.
  • The formula contains division - the result is recomputed fresh at each time grain. Ratios can’t be added across periods - the sum of three monthly ratios isn’t the quarterly ratio - so the formula is evaluated again whenever the time grain changes.
  • The formula combines operands with different time aggregations - for example, mixing a Sum metric like Revenue with an End of period metric like Headcount. This is sometimes intentional (Revenue ÷ Headcount = Revenue per Employee) and sometimes a mistake. Review the formula carefully before saving.

What’s supported in a formula 

The formula editor supports a small, finance-friendly set of building blocks.

What you can use in a formula:

  • Metrics - any existing metric in your workspace, inserted as a chip.
  • Operators - +, -, ×, ÷. Standard order of operations applies: multiplication and division are evaluated before addition and subtraction.
  • Numeric constants - fixed numbers, useful for ratios or scaling (for example, multiplying by 100 to convert a decimal to a percentage).
  • Days in period - the built-in value covered above.
  • Periods in year - the built-in value covered above.
  • Parentheses - group operations to override the default order, for example (Revenue - COGS) ÷ Revenue.

How missing and zero values are handled:

Missing values are handled differently depending on the operator. Understanding these rules matters because some operators can silently produce plausible-looking results from incomplete input data.

  • Addition (+) and Subtraction (-) treat a missing value as zero. For example, 100 + (null) = 100, and 100 - (null) = 100. If both operands are null, the result is 0.
  • Multiplication (×) and Division (÷) return a null result whenever any operand is null. For example, 100 × (null) = null, and 100 ÷ (null) = null.
  • Division by zero returns a null result rather than an error.

What you will see when data is missing:

Example 1 - Total expense across business units. You’re viewing Total Expenses for January, summed across 5 business units. One BU hasn’t loaded its expense data yet. The total shows the sum of the four BUs that did report; the missing BU silently contributes zero. The displayed number gives no visible indication that one input is missing.

Example 2 - Gross Margin %, Revenue is missing. Formula: (Revenue - COGS) ÷ Revenue. For January, Revenue hasn’t loaded yet. The result is null - the divisor is missing, so no margin can be calculated.

Example 3 - Gross Margin %, COGS is missing. Same formula. Revenue is $100K but COGS hasn’t loaded. The subtraction treats the missing COGS as zero, so the numerator becomes $100K, and the result is 100% margin. The number looks plausible until COGS arrives. If a margin looks unusually high or otherwise unexpected, check whether all underlying inputs are loaded.

Example 4 - CAC, no new customers. Formula: Marketing Expenses ÷ New Customers. In a month with zero new customers, the result is null, not an error or infinity.

What this means in practice:

A clean-looking number is not always a complete picture. When using Addition or Subtraction - or formulas that contain them - missing values are silently treated as zero. Even ratios can produce plausible-looking results from incomplete inputs (Example 3). If completeness matters for a decision, confirm that the source data is fully populated.

A null cell signals a missing input. Multiplication and division return null whenever any operand is missing, so a null Gross Margin % or null CAC tells you the underlying data isn’t all there - not that the metric is zero.

Add dimensions 

Dimensions are the slices users can break the metric down by - for example, Region, Department, or Country.

  1. Click + Add dimensions.
  2. In the dropdown, check the dimensions you want to include for this metric. The Preview updates instantly to show breakdowns by the selected dimensions.
  3. To create or edit dimensions, click Manage dimensions at the bottom of the dropdown. See Managing dimensions for details.

 

For a calculated metric, the list shows only dimensions that are mapped to every source table of the metrics used in the formula. A dimension that’s mapped on some metrics but not others is shown disabled, with the tooltip: “This dimension isn’t selected in all metrics used by this formula. Add the missing mappings to use it.” Update the dimension’s mappings to enable it.

Choose a format 

The Format setting controls how the metric’s result is displayed by default wherever the metric is used - in Finance OS, AI agent answers, and any other consumption surface. Use the format toolbar to set:

  • Number type and currency symbol - Currency ($, €), Percentage, or plain Decimal.
  • Decimal places - increase or decrease using the decimal-arrow buttons.
  • Thousands separator - toggle on or off.

A Sample next to the toolbar previews the formatting in real time so you can confirm how the value will appear to consumers.

 

Name and describe the metric 

  1. Enter a Name that your team will recognize (for example, Net income or Gross Margin %). Use the business name, not a technical label.
  2. The Semantic Layer automatically generates a draft Description based on the metric’s name and formula. The sparkle icon next to Description - with the tooltip Generated by AI - indicates the description came from AI.
  3. Review the description. Edit or replace it to match how your team actually uses the metric. Once you edit the description, the AI indicator updates to show the description is now yours.

A clear, business-language description helps consumers understand what the metric measures and when to use it. We recommend reviewing the AI-generated draft and adjusting it for your team’s vocabulary.

Choose a category 

A category groups related metrics together - for example, Profitability, P&L, Workforce, or SaaS KPIs - so your team can find them quickly in the Metrics page.

Pick an existing category

  1. Click the Category field. The dropdown lists every existing category.
  2. Click the category you want, or start typing to filter the list.

Create a new category

You can add a new category in two ways:

  • From the dropdown: click + Add new category at the top of the list. The row converts to an inline text input. Type the new category name and press Enter or click the confirm icon.
  • Directly in the field: type the new category name into the Category field. A suggestion row appears with + Add ‘<your text>’ category. Press Enter or click the suggestion to create and select the category.

Category is also useful metadata for AI agents and other consumption surfaces, helping them understand which metrics belong together.

Save the metric 

  1. Review the editor for any validation messages.
  2. Click Save.
  3. The metric appears in the Metrics & dimensions page with the calculated-metric icon, and is ready to use.

 

View a metric’s definition 

Anyone with access to a calculated metric can open its definition without editing it - useful for confirming exactly how the formula is constructed before using it.

  1. On the Metrics & dimensions page, locate the metric you want to inspect.
  2. Click the three-dots menu (⋮) next to the metric and choose See details option to open the read-only view.
  3. Review the formula, dimensions, format, and description. Close the view when you’re done.

Edit a calculated metric 

You can change a calculated metric’s definition at any time. Edits take effect for every consumer of the metric, so be deliberate when changing the formula or format.

  1. On the Metrics & dimensions page, locate the metric you want to edit.
  2. Click the three-dots menu (⋮) next to the metric and choose Edit.
  3. Update the fields you need to change.
  4. Click Save.

Changing the formula, dimensions, or format of a metric that’s already in use can change the values seen by every consumer downstream. The Semantic Layer asks you to confirm before saving these changes.

Delete a calculated metric 

  1. On the Metrics & dimensions page, locate the metric you want to delete.
  2. Click the three-dots menu (⋮) next to the metric and choose Delete.
  3. Confirm the deletion.

A calculated metric that’s referenced by another metric cannot be deleted. Remove the reference from the dependent metric first, then delete.



© Datarails Ltd. All rights reserved.

Updated

Was this article helpful?

0 out of 0 found this helpful

Have more questions? Submit a request

Comments

0 comments

Please sign in to leave a comment.