Custom Report Builder: Formula Fields

Smart CRM & Sales Hub Dashboards & Reporting
,
1

Trying to create a report that is a count of our total Deals that are marked as Closed Won or Closed Lost as a single count. Saw the formula field beta but it doesn’t seem to be working as I want it to.
I thought I could do a COUNT([DEAL.hs is closed won]=TRUE)+COUNT([DEAL.hs is closed lost]=TRUE) but seems like COUNT is not an available function for the formula.
Is anyone aware of a workaround for how I might be able to create this report/view for my dashboard?

2

Hi @PHA_iPadag,

Have you tried just using the filters in the custom report builder instead of the formula field? Since COUNT() isn’t supported in formulas yet, a good workaround is to filter the report to only show deals where the stage is Closed Won OR Closed Lost, and then use “Count of deals” as the metric.

It’s not a formula-based solution, but it should give you (close to) what you’re looking for on the dashboard.

Best,

Jess

3

Hi @JessicaBaskey ,
Yes, that’s what my initial report/dashboard has! I guess that’s my only solution for the time being. I was facing some pushback by my users since the metric on the dashboard reads “Count of Deals”. I was hoping that creating the formula field would let me override that “Count of Deals” with essentially the label they would want for the dash.
I appreciate your insight and thanks for responding!
Cheers,
Ianne

4

Super stoked about the formula fields coming out, but not fully there yet!

We have tons of teams getting around this and other current report limitations using Coefficient’s HubSpot-certified 2-way sync with Google Sheets and Excel.
For this use case, you’ll:

  1. Create the field you want to populate in HubSpot.
  2. Pull your deal data into Sheets using Coefficient, including deal stage, status, and any other fields you need.
  3. Add a new column into your data fields to build the count formula you need.
  4. Push the calculated field back into HubSpot.

You can do all of this inside of your spreadsheet in probably 10 minutes and keep it on auto-refresh. It’s much faster than fighting with the current formula limitations in the Custom Report Builder, and gives you the flexibility to evolve the logic as your needs change. Let me know if you want help getting it set up, it’s super quick to do!

5

I hit this same wall with a client last month. COUNT() isn’t in the supported formula functions yet, it only works as the report’s base count, not inside a custom formula.

  • Instead of one combined metric, filter for Deal stage is any of Closed Won, Closed Lost, then use the report’s own count (not a formula) as your number
  • You can’t rename “Count of Deals” in reports today, but you can add a report title or description that explains it, or use a single object list report where you control the column header
  • If the label really matters, a calculated property that sets a “Closed” checkbox to true for both stages works, then report off that property with COUNT

Not as clean as you want but it gets you the number without waiting on formula field COUNT support.

6

You can do this using a formula field, math and the aggregation option. It does require knowing the internal pipeline Ids, however.

  1. Create a new Report using Deal as the primary source.

  2. Add (Count) deals to the first column.

  3. Create a Formula field:

    This is the inefficient formula but easier to manage for most people. What this does is return a value of 1 if the deal stage is a specific number. In this case, it will check the closed won internal ids. So I have 7 pipelines and there are 7 closed won stages.

    If it hits any of them, it will return the value of 1. Otherwise, 0 is returned.

    IF([DEAL.dealstage] == "92111900" OR [DEAL.dealstage] == "191118000" OR [DEAL.dealstage] == "177110700" OR [DEAL.dealstage] == "12118000" OR [DEAL.dealstage] == "4311200" OR [DEAL.dealstage] == "1011246500" OR [DEAL.dealstage] == "1211952700", 1, 0)

  4. Then on report, edit the column to SUM.

  5. In the unsummarized data, you can see that if a Deal is Closed Won, it will return 1. Otherwise, it will return 0. Since we’re summing this column, it will return the sum of all the closed won deals since any other stage would have a value of 0.

  6. You can do the same for closed lost or any other group of stages.

  7. You can see my example report below

    image
    image1120×262 10.4 KB