Formula fields can now be created in the custom report builder, allowing you to perform row-level math on your existing data and create new reporting fields on the fly.
Reporting is a journey of turning data into information, which then generates insights to fuel decisions that lead to valuable impact. In this journey to value, gathering insights has been one of the most important connection points. In the past, gathering insights about your data has required additional work outside of HubSpot.
If you needed to provide a different vantage point about your data, whether it be displaying revenue less any commission payouts or performing a simple conversion rate between two numbers, you would have to export your data and attempt to piece together these insights in outside tools. Not only is this time-intensive, but it inevitably clutters up your CRM with static, one-off data points that are only meant to be used for reporting. This also creates a disjointed experience with data analysis and forces you to outsource your needs instead of solving them right within HubSpot.
Well, those days are over! The ability to perform row-level calculations has been the missing link in report building and is now available in the custom report builder. This feature enables the creation of new reporting fields on the fly and gives you a taste of the raw power of datasets, a core data transformation tool in Operations Hub Enterprise.
Formula fields is opening up a whole new level of insight generation, allowing you to transform your data more easily and faster than ever before.
Formula fields can be created right from the custom report builder's editor, with quick access at the bottom of the left-hand fields selection panel.
Formula fields are powered by the same advanced formula editor found in Datasets, minus the Functions option, unless you are an Operations Hub Enterprise customer. You can input any integers into this expression editor, as well as do arithmetic on any Number property available in your report, regardless of what data source it belongs to.
To allow you full control over your report building workspace, you are able to drag and move the formula builder anywhere on your screen, as well as collapse the window or expand it to full size.
Move
Collapse
Expand
You'll start the formula build in the "Field info" step, where you'll write out your formula and select properties to include in your calculation. You can either use the "Insert" option to search and select the intended property or type its name directly into the editor, which will bring up a suggested list of properties to choose from.
Once you have written out your formula, you'll move to the "Review" step, where you'll be able to preview your formula and ensure the data is being calculated correctly. Properties with NULL values—which are presented as "(No value)" in HubSpot—will impact the output of your formula, depending on how you decide to treat these empty values. You can choose to keep these "No value" distinctions as is—meaning, those rows will not be counted in the formula field's row-level calculation—or you can choose to convert those "No values" into zeros.
For example, if you wanted to calculate the amount of revenue you brought in, minus any discounts that were provided, you would want the deals without any discounts to have a discount represented as "0." Otherwise, you would be leaving out revenue that should be accounted for.
When seeing the formula field in your report, you can see from the visualization and the drill-down table that the formula field is properly calculating the final amounts you want because the "No value" rows are being accounted for.
However, it is important to know when to convert "No values" to zeros, since there are instances when you want empty values to simply not count towards your calculations and aggregations. For example, say you wanted to manually add a $100 discount to all of your deals that have a known amount.
If you decided to 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 adding a formula field to your report's configuration, you will have all of the same options as other Number properties, such as formatting and aggregation.
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.
Row-level
Column-level
To provide feedback about this beta for the HubSpot product team, please fill out this form.
CMS Enterprise, CMS Pro, Marketing Enterprise, Marketing Pro, Operations Enterprise, Operations Pro, Sales Enterprise, Sales Pro, Service Enterprise, Service Pro