What are custom calculations?
Custom calculations let you define dynamic metrics and transformations without modifying dataset queries. This article explains how to use custom calculations in AI/BI dashboards.
Why use custom calculations?
Custom calculations allow you to create and visualize new fields from existing dashboard datasets without changing the source SQL. You can define up to 200 custom calculations per dataset.
Custom calculations are one of the following types:
- Calculated measures: Aggregated values such as total sales or average cost. Calculated measures can use the
AGGREGATE OVERcommand to compute values across time ranges. - Calculated dimensions: Unaggregated values or transformations such as categorizing age ranges or formatting strings.
Custom calculations behave similarly to metric views, but are scoped to the dataset and dashboard where they are defined. To define custom metrics that can be used with other data assets, see Unity Catalog metric views.
Create dynamic metrics with calculated measures
Suppose you have the following dataset:
Item | Region | Price | Cost | Date |
|---|---|---|---|---|
Apples | USA | 30 | 15 | 2024-01-01 |
Apples | Canada | 20 | 10 | 2024-01-01 |
Oranges | USA | 20 | 15 | 2024-01-02 |
Oranges | Canada | 15 | 10 | 2024-01-02 |
You want to visualize profit margin by region. Without custom calculations, you would need to create a new dataset with a margin column:
Region | Margin |
|---|---|
USA | 0.40 |
Canada | 0.43 |
While this approach works, the new dataset is static and might only support a single visualization. Filters applied to the original dataset do not affect the new dataset without additional manual adjustments.
With custom calculations, you can express the profit margin as an aggregation using the following formula:
(SUM(Price) - SUM(Cost)) / SUM(Price)
This measure is dynamic. When used in a visualization, it automatically updates to reflect filters applied to the dataset.