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.
Move: click and drag the header to move the formula builder window.
Collapse: click the downarrow icon in the header to collapse the builder window.
Expand: click the enlargeexpand 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.
Please note: setting the no value to a zero has no impact on the original property, it only treats it as zero for that specific report.
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.
If you do not have access to Datasets in Operations HubEnterprise, you can add one formula field per custom report. If you have access to Datasets, you can create up to three formula fields per report, allowing you to reference and insert any other formula fields previously created for your report.
Functions are available to Operations Hub Enterprise only. You can input any integers into the expression editor, as well as do arithmetic on any Number property available in your report, regardless of what data source it belongs to.
Thank you for your feedback, it means a lot to us.