Metric Transforms

Overview

Metric transforms apply analytical window functions to your metrics in the Explorer. They take already-aggregated values (like SUM(Revenue)) and compute running totals, moving averages, period deltas, or percent-of-total calculations across rows.

Four transform types:

How It Works

Metric transforms operate as a second pass over your query results:

1. Aggregation runs first -- your base query groups and aggregates data (e.g., SUM(Revenue) GROUP BY month)
2. The window function applies -- the transform wraps the aggregated metric in a SQL window function (e.g., SUM(metric) OVER (ORDER BY month))
3. Results return -- you see both the original metric and the transformed values

This is why transforms require at least one group-by field. Without grouping, there are no rows to window over.

Getting Started

1. Add a numeric metric to your visualization (e.g., SUM of Revenue)
2. Click on the metric to expand its configuration
3. Open the Metric Transform section
4. Select a transform type from the dropdown
5. Configure the parameters (order-by field, direction, window size, etc.)

Semaphor auto-detects the first date field in your group-by columns as the default Order By field. You can change this to any other group-by field.

Transform Types

Running Total

Computes a cumulative sum of the metric, row by row, in the order you specify.

SUM(metric) OVER (
  PARTITION BY region
  ORDER BY order_date ASC
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

Parameters:

Example: Cumulative Revenue Over Time

Group by Order Date (month), metric: SUM(Revenue), transform: Running Total.

Each row adds the current month's revenue to the running total of all previous months.

Moving Average

Computes a rolling average over a sliding window of N rows.

AVG(metric) OVER (
  PARTITION BY region
  ORDER BY sale_date ASC
  ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
)

Parameters:

Example: 7-Day Moving Average of Daily Sales

Group by Sale Date (day), metric: SUM(Sales), transform: Moving Average, window size: 7.

Early rows use fewer data points. Once the window fills (Day 7 onward), each value averages exactly 7 days.

Previous Period Delta

Computes the difference between the current row and the previous row. Available in two modes:

• Absolute -- raw difference (current - previous)
• Percent -- percentage change ((current - previous) / previous)

metric - LAG(metric, 1) OVER (
  PARTITION BY region
  ORDER BY order_date ASC
)

(metric - LAG(metric, 1) OVER (...))
  / NULLIF(LAG(metric, 1) OVER (...), 0)

Parameters:

Example: Month-over-Month Revenue Change

Group by Date (month), metric: SUM(Revenue), transform: Previous Period Delta.

The first row has no previous value to compare against, so the delta is empty.

Percent of Total

Expresses each row's metric as a percentage (or fraction) of the total. Available in two scopes:

• Grand Total -- each row divided by the overall sum across all rows
• Partition Total -- each row divided by the sum within its partition group

metric / SUM(metric) OVER ()

metric / SUM(metric) OVER (PARTITION BY category)

Parameters:

Example: Regional Revenue Contribution

Group by Region, metric: SUM(Revenue), transform: Percent of Total, scope: Grand Total.

Partitioning (Reset By)

Partitioning controls when a window calculation resets. Without partitioning, the window spans all rows. With partitioning, it restarts for each distinct value of the partition fields.

Example: Running Total with Region Partitioning

Group by Region + Month, metric: SUM(Revenue), transform: Running Total, order by: Month, reset by: Region.

The cumulative sum resets to zero when the region changes. Without the Region partition, the running total would accumulate across all regions continuously.

By default, Semaphor uses all group-by fields except the order-by field as partitions. You can toggle individual fields on or off in the Reset By section.

Compatibility

Supported Chart Types

Database Support

Metric transforms work with all supported database dialects: PostgreSQL, MySQL, BigQuery, Redshift, Snowflake, ClickHouse, DuckDB, and MSSQL.

Constraints

• The metric must be numeric (SUM, AVG, COUNT, MIN, MAX, or COUNT DISTINCT)
• At least one group-by field is required
• Metric transforms and metric comparisons cannot be used on the same metric simultaneously -- use one or the other

Best Practices

Choose appropriate order-by fields. Date fields are the most natural choice for time-series analysis. Running totals and moving averages over non-temporal fields can produce confusing results unless the ordering is meaningful.

Use partitioning to avoid misleading totals. A running total across unrelated categories (e.g., cumulating revenue across both "North" and "South" without partitioning) often produces numbers that are hard to interpret. Partition by the category to get per-group cumulations.

Match window size to data granularity. For moving averages, align the window to a meaningful period: 7 for daily data (weekly average), 4 for weekly data (monthly average), 12 for monthly data (yearly average).

Control partial-window behavior. Set min periods equal to window size if you want to suppress early rows where the moving average is based on fewer data points than intended.

Percent of total with partition scope. Use Partition Total when you want "percent within category" analysis (e.g., each product's share within its category). Use Grand Total when you want each row's share of the entire dataset.

Additional Resources

• Calculated Fields -- create derived metrics and dimensions
• Semantic Domains -- metric transforms work with domain fields
• Filters -- combine transforms with dashboard-level filtering