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 modifying the source SQL. You can define two types of custom calculations:

• Calculated measures: Aggregated values such as total sales or average cost
• Calculated dimensions: Unaggregated values or transformations such as categorizing age ranges or formatting strings

Example: calculated measure

Suppose you have the following dataset:

You want to visualize profit margin by region. Without custom calculations, you would need to create a new dataset with a margin column:

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 it's used in a visualization, it automatically updates to reflect filters applied to the dataset.

Example: calculated dimension

Calculated dimensions let you define unaggregated values or lightweight transformations without changing the source dataset. This is helpful when you want to organize or reformat data for visualization.

For example, to analyze age trends by age group instead of individual ages, you can define a custom age_group dimension using the following expression:

CASE
   WHEN age < 18 THEN '<18'
   WHEN age >= 18 AND age < 25 THEN '18–24'
   WHEN age >= 25 AND age < 35 THEN '25–34'
   WHEN age >= 35 AND age < 45 THEN '35–44'
   WHEN age >= 45 AND age < 55 THEN '45–54'
   WHEN age >= 55 AND age < 65 THEN '55–64'
   WHEN age >= 65 THEN '65+'
END

Performance benefits

Custom calculations are optimized for performance. When the dataset result is 100,000 rows or fewer, or 100MB or smaller (whichever is less), filtering and aggregation are handled in the browser. This improves dashboard responsiveness, especially when applying filters. For larger datasets, calculations are processed by the SQL warehouse. For more details, see Dataset optimization and caching.

Create a custom calculation

This example creates a calculated measure based on the samples.nyctaxi.trips dataset. It assumes general knowledge about how to work with AI/BI dashboards. If you are unfamiliar with authoring AI/BI dashboards, see Create a dashboard to get started.

1. From the Data tab, create a dataset using the following statement:

SELECT * FROM samples.nyctaxi.trips

1. Rename the dataset Taxicab data.

2. Click Custom Calculation.

   

3. A Create Calculation panel opens on the right side of the screen. In the Name text field, enter Cost per mile.

4. (Optional) In the Comment text field, enter “Uses the fare amount and trip distance to calculate cost per mile.”

5. In the Expression field, enter the following:

   try_divide(SUM(fare_amount), SUM(trip_distance))
   

6. Click Create.

View the schema

Click the Schema tab in the results panel to view the custom calculation and its associated comment.

Calculated measures are listed in the Measures section and marked by a fx. The value associated with a calculated measure is dynamically calculated when you set the GROUP BY in a visualization. You cannot see the value in the results table. Calculated dimensions appear in the Dimensions section.

Use a custom calculation in a visualization

You can use the previously created Cost per mile calculated measure in a visualization.

1. Click Canvas. Then, place a new visualization widget on the canvas.
2. Use the visualization configuration panel to edit the settings as follows:
   • Dataset: Taxicab data
   • Visualization: Bar
   • X axis:
     • Field: dropoff_zip
     • Scale Type: Categorical
     • Transform: None
   • Y axis:
     • Cost per mile

The following image shows the chart.

Visualizations that include custom calculations dynamically update based on applied filters. For example, if you add a filter for pickup_zip to the canvas and select a filter value, the visualization updates to display the cost per mile metric only for trips originating from the selected filter value. The resulting bar chart reflects the filtered data accordingly.

Edit a custom calculation

To edit a calculation:

1. Click the Data tab and then click the dataset associated with the calculation you want to edit.
2. Click the Schema tab in the results panel.
3. Measures and Dimensions appear under the list of dataset fields. Click the kebab menu to the right of the calculation you want to edit. Then, click Edit.
4. In the Edit custom calculation panel, update the text fields that you want to edit. Then, click Update.

Delete a custom calculation

To delete a calculation:

1. Click the Data tab and then click the dataset associated with the measure you want to edit.
2. Click the Schema tab in the results panel.
3. The Measures section appears under the list of fields. Click the kebab menu to the right of the calculation that you want to edit. Then, click Delete.
4. Click Delete in the Delete dialog that appears.

Limitations

To use custom calculations, the following must be true:

• Columns used in the expression must belong to the same dataset.
• Expressions that reference external tables or data sources aren't supported and might fail or return unexpected results.

Supported functions

The following tables list supported functions. Attempting to use an unsupported function results in an error.

Aggregate functions

All calculated measures must be aggregated. The following aggregation operations are supported:

Arithmetic operations

You can combine expressions with the following arithmetic operations:

Cast functions

Use the following functions to cast values to a specified type:

Date, timestamp, and interval functions

Use the following functions to work with dates, timestamps, and intervals:

String functions

Use the following functions to transform strings:

Miscellaneous functions

The following functions are also supported: