Create formula fields in the custom report builder

Create row-level calculations using your existing data in your HubSpot account. Use formula fields to create reports on revenue generated minus any discounts, profit margin calculated into amount totals, or projections on future growth by adding in conversion rates.

Formula fields perform row-level math on your data table, which means the calculation is performed on a row-by-row basis. This is different to aggregate math, which is performed on the column level.

Before you get started

Please note the following before you begin creating formula fields:

• This feature is available to all Professional and Enterprise subscriptions. However, the Functions tool within formula fields is only available in accounts with access to datasets (Data Hub Enterprise only).
• The number of formula fields you can add to a report depends on your subscription. Aside from cloning a report, formula fields are not reusable across reports—if you need reusable logic across multiple reports, use datasets instead.
• Formula fields perform row-level math on your data table, which means the calculation is performed on a row-by-row basis. This is different than aggregate math, which is performed on the column-level.

Create formula fields in the custom report builder

To create formula fields:

1. In your HubSpot account, navigate to Reporting > Reports.
2. In the upper right, click Create report.
3. In the Build from scratch section, click Custom Report Builder. 
4. Select your data sources.
5. In the bottom left panel, next to Formula fields, click Add +.
   
   
   
6. In the pop-up window, enter a name for the field.
7. In the Define formula field, enter your formula.
8. To insert a property you've added to the dataset, click the Insert field dropdown menu, then select the property.
9. To insert a function, click the Insert function dropdown menu, then select a function. Learn more about functions.
   • As you create formulas, the Define formula field will display a validation status. When a formula is invalid, a red status indicator will display. Click the issue to view error details.
     
     
     

10. Below the Define formula field, you can preview your formula.
11. Toggle the Change null values to zero? switch ON to treat all No values as zeros. If you leave this switch OFF, rows with No value will be excluded from the formula field’s row-level calculation.

For example, if you wanted to calculate the amount of revenue you created, minus discounts that were provided, you would want deals without discounts to have a discount represented as "0." Otherwise, you would be excluding revenue that should be included.

There are scenarios when you want empty values to not count towards your calculations and aggregations. For example, if you wanted to manually add a $100 discount to all of your deals that have a known amount.

If you convert No values to a zero, it would create a negative amount if a deal doesn't have an Amount listed, whereas keeping the empty values as-is skips over the row and doesn't apply the automatic discount.

12. When you are finished, click Create formula.
13. The newly created formula field can be found in the Formula fields section in the left sidebar.
14. To add the formula field to your report, click and drag the field.