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:

Item	Region	Price	Cost
Apples	USA	30	15
Apples	Canada	20	10
Oranges	USA	20	15
Oranges	Canada	15	10

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:

SQL

(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:

SQL

CASE
   WHEN age < 18 THEN '<18'
   WHEN age BETWEEN 18 AND 24 THEN '18–24'
   WHEN age BETWEEN 25 AND 34 THEN '25–34'
   WHEN age BETWEEN 35 AND 44 THEN '35–44'
   WHEN age BETWEEN 45 AND 54 THEN '45–54'
   WHEN age BETWEEN 55 AND 64 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:

SQL

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 Description text field, enter “Uses the fare amount and trip distance to calculate cost per mile.”

5. In the Expression field, enter try_divide(SUM(fare_amount), SUM(trip_distance)).

6. Click Create.

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

note

Custom calculations cannot be used with table visualizations.

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.
• The expression cannot include calls to external tables or data sources.

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:

Aggregation	Description
avg(expr) or mean(expr)	Returns the calculated mean in a column or expression.
count(*)	Returns the number of rows in a group.
count(DISTINCT expr)	Returns the number of unique rows in a group.
sum(expr)	Returns the total of values in a column or expression.
max(expr)	Returns the maximum value in a column or expression.
min(expr)	Returns the minimum value in a column or expression.
percentile(expr, percentage [,frequency])	Returns the exact percentile value of expr at the specified percentage in a group.
first(expr [,ignoreNull])	Returns the first value of expr for a group.
last(expr [,ignoreNull])	Returns the last value of expr for the group.
count_if	Returns the count of rows that satisfy a given condition.
median	Returns the median of a set of values.
stddev	Returns the standard deviation of a set of values.
variance	Returns the variance of a set of values.

Arithmetic operations

You can combine expressions with the following arithmetic operations:

Operation	Description
expr1 + expr2	Returns the sum of expr1 and expr2.
expr1 - expr2	Returns the difference when subtracting expr2 from expr1.
multiplier * multiplicand	Returns the product of two expressions.
dividend / divisor	Returns the result of dividing the dividend by the divisor.
- expr	Returns the negated value of the expression.
+ expr	Returns the value of the expression.
try_add(expr1, expr2)	Adds two values. If an error occurs, returns NULL.
try_subtract(expr1, expr2)	Subtracts expr2 from expr1. If an error occurs, returns NULL.
try_multiply(multiplier, multiplicand)	Multiplies two numbers. If an error occurs, returns NULL.
try_divide(dividend, divisor)	Divides the dividend by the divisor. If an error occurs, returns NULL.
pow or power	Returns the result of expr1 raised to the power of expr2.

Cast functions

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

Function	Description
cast(expr AS type)	Casts the value expr to the target data type type.
try_cast(expr AS type)	Casts the value expr to the target data type type safely.

Date, timestamp, and interval functions

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

Function	Description
datediff(endDate, startDate)	Returns the number of days from startDate to endDate.
timestampdiff(unit, start, stop)	Returns the difference between two timestamps measured in units.
date_format(expr, fmt)	Converts a timestamp to a string in the format fmt.
timediff(unit, start, stop)	Returns the difference between two timestamps measured in units.
date_part	Extracts a specific part, such as year, month, or day, from a date or timestamp.
date_trunc	Truncates a date or timestamp to a specified unit,such as year or month.

String functions

Use the following functions to transform strings:

Function	Description
concat(expr1, expr2[, …])	Returns the concatenation of the arguments.
concat_ws(sep[, expr1[, …]])	Returns the concatenation of strings separated by sep.

Miscellaneous functions

The following functions are also supported:

Function	Description
CASE expr { WHEN opt1 THEN res1 } […] [ELSE def] END	Returns resN for the first optN that equals expr or def if none matches.
CASE { WHEN cond1 THEN res1 } […] [ELSE def] END	Returns resN for the first condN evaluating to true, or def if none found.
coalesce(expr1, expr2 [, …])	Returns the first non-null argument.
nvl(expr1, expr2)	Returns expr2 if expr1 is NULL, or expr1 otherwise.