Create formula fields in the custom report builder (BETA)

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.

• In your HubSpot account, navigate to Reporting > Reports.
• In the upper right, click Create report.
• In the Build from scratch section, click Custom Report Builder. 
• Select your data sources.
• In the bottom left, click Create formula field.

• Configure how the window displays in the editor:
  • Move: click and drag the header to move the formula builder window.
  • Collapse: click the down arrow icon in the header to collapse the builder window.
  • Expand: click the enlarge expand icon in the header to expand the builder window.
• In the pop-up window, enter a name for the field.
• In the Formula field, enter your formula.
• To insert a property you've added to the dataset, click the Insert field dropdown menu, then select the property.

• To insert a function (Operations Hub Enterprise only), click the Insert function dropdown menu, then select a function. Learn more about functions.

• As you create formulas, the Formula field will display a validation status. When a formula is invalid, a red status indicator will display and you can click the issue to view error details.

• Once you've set up your field, click Next in the bottom right.
• In the Formula review screen, preview your formula. Click back to edit your formula.
• Select how to calculate formulas when a field has no value.
  • Keep"(no value)" as-is: the rows with No value will not be counted in the formula field's row-level calculation.
  • Treat "(no-value)" as zero: convert the No values into zeros.

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.

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