topic Report to calculate average on sum in Dashboards & Reporting

Report to calculate average on sum

WVanBalen — Wed, 06 Sep 2023 12:17:14 GMT

Hi,

I like to create a report telling me the average monthly revenue per fiscal year (so for the current year this should be from the first month of the fiscal year till the current month minus 1).

So we have multiple deals within a month. I can make a report that shows me the revenue sum of each month (and I can configure it to take in account all deals till last month). From those values I like to have a report telling me the average monthly revenue for each year.

It that possible?

Re: Report to calculate average on sum

Josh — Wed, 06 Sep 2023 15:49:26 GMT

Hi @WVanBalen,

You should be able to accomplish this using close date monthly as your X axis and Amount using the Average aggregation as your Y axis. From there you can apply close dates based on your fiscal year.

Josh

Re: Report to calculate average on sum

WVanBalen — Fri, 08 Sep 2023 07:44:36 GMT

That's not what I need.

To explain furter, I hadded some data in Excel as below.

So multiple deals per month. Those deals result in a sum per month of amount X.

We do that for each month, so we have X1, X2, X3,... X12 for 1 year.

Then we take an average over the X values.

So for 2023, we only have data till X8, so we should average from X1-2023 till X8-2023.

The result I want is an overview per year, like this:

Any idea for this?

Re: Report to calculate average on sum

Josh — Fri, 08 Sep 2023 12:22:17 GMT

Hi @WVanBalen,

Got it...the additional breakdown makes it tough to accomplish in HubSpot. I've played around with the report builder and the sales analytics tools and believe you'll have to accomplish this outside of HubSpot. I was hoping a pivot table in HubSpot could do it, but it doesn't allow the additional breakdown.