Create and use summary functions in datasets
Last updated: January 29, 2024
Available with any of the following subscriptions, except where noted:
Operations Hub Enterprise 
A dataset is a collection of data from across your HubSpot account that can then be used in custom reports. A dataset can include properties for CRM objects and HubSpot assets, along with formulas to calculate your data as needed.
Summary functions enable you to aggregate data across multiple rows within datasets. It also enables you to calculate rates and ratios.
Learn more about summary functions and view videos on the HubSpot Community.
Metrics that can be calculated using summary functions include:
 Conversion rate
 Average order size
 Average margin
 Close ratio
 Lifetime value
 Customer acquisition cost
 Churn rate
 SLA adherence
 Custom NPS calculations
Rowlevel vs summary formulas
Rowlevel formulas: execute rowlevel operations and make calculations across a single record. The formula result displays one value per row, and all context for the calculation exists at a row level. Find more information in the syntax section of the datasets knowledge base.
Summary formulas: execute multirow data operations and calculate metrics across multiple records. The formula result displays one value per row, but the context for the calculation may contain more than one row of data.
Use summary functions
When creating a Formula field, you can use the below summary functions to create functions:
List of summary functions
Function  Definition  Input/Output  Syntax  Example 

Adds values in a given column and returns an aggregate value. 
Input: Number/Null Output: Number/Null 



Returns the number of rows in a given column. 
Input: Number/Null Output: Number/Null 



Calculates the average value of a column of numbers. The average value of a column is equal to the sum of the values divided by the number of values. 
Input: Number/Null Output: Number/Null 



Returns the lowest value in a given column or the lowest value between two values on the same row. 
Input: Number/Date/Datetime/Null Output: Number/Date/Datetime/Null 



Returns the highest value in a given column or the highest value between two values on the same row. 
Input: Number/Date/Datetime/Null Output: Number/Date/Datetime/Null 


Create a summary function
Columns calculated with summary functions can be used in the custom report builder as a measure. Measures are numerical or quantitative values that can be broken down using dimensions.
Within the dataset builder, summary measures will repeat results on each row. The calculation defaults to a table calculation until used in the custom report builder where the report configuration defines the summary level of the measure.
Learn more about summary formulas in datasets and view examples.
Create row level summary results
You can also use summary functions to create row level results that will ignore any reporting dimensions. This requires using a FIXED() function.
Function  Input/Output  Syntax  Example 

Input: Summary expression Output: Rowlevel result 


Please note: if no optional value is defined in the FIXED() function, the calculation will be computed at the table level and will ignore all dimensions in the report view.
Example
Track conversion or win rate
Win rate refers to the proportion of successful deals out of the total number of opportunities. By tracking win rates based on product, market, target audience, and other factors, you can pinpoint the chances of success for each opportunity. This will enable you to strategically direct your resources toward those with the highest conversion potential.
 Data source needed: Deals
 Type of metric: Flexible measure
 Formula:
DISTINCT_COUNT(IF(LABEL([DEAL.dealstage]) = "Invoiced", [DEAL.hs_object_id], NULL)) / DISTINCT_COUNT(IF([DEAL.hs_is_closed] = true, [DEAL.hs_object_id], NULL))
View this example below in the datasets editor:
This conversion rate formula can then be used in reports, for example: