Skip to content

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

Row-level vs summary formulas

Row-level formulas: execute row-level 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 multi-row 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 



SUM(IF([Is Closed Won] = true, 1,0))


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


AVERAGE ([Deal.Amount])



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: Row-level result 


FIXED (SUM([DEAL.Amount]), [DEAL.pipeline])

SUM(IF([Is Closed Won] = true, 1,0), [DEAL.hubspot_owner_id])

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.


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:

Screenshot 2023-09-25 at 10.33.19 AM


Was this article helpful?
This form is used for documentation feedback only. Learn how to get help with HubSpot.