Calculate the average number of tickets per user

[enorberg]

I'm trying to set up a report that shows the average number of Hubspot tickets per user. My definition of "Users" is a custom property on the company object with that same name - each customer has this filled in. So to achieve this calculation, we would need to create a calculation along the lines of:

Count of tickets / Sum of users

If I understand things correctly, I can only do these calculations on a record level in Hubspot, then display the unweighted overage of that. Of course, this gets really skewed. If I only have 2 customers, one large (5,000 users / 50 tickets) and one small (5 users / 5 tickets), the calculation should look like this:

(50+5) / (5,000+5) = 0,01 tickets / user

Instead, Hubspot would calculate that the large customer has (50/5000=) 0,01 tickets/user and the small customer (5/5=)1 ticket/user and give me an average of 0,5 tickets per user, which of course isn't true.

Is there a way around this?

I have Operations Hub Enterprise.

[deepikaverma · Accepted solution]

Hi @enorberg ,

Thank you for posting on HS community!

Have you tried it via Datasets or by calculation property?

Datasets are available in operation Hub enterprice subscription, with data sets you cab create the custom formula also for reports.
Create and use datasets (hubspot.com)

- Also, can you please share the snapshots of the report you have buit as of now?

Thank you!

[enorberg]

@deepikaverma Thanks for your reply. Yes, I've tried data sets, not for this specific problem but for similar ones. I could of course be using them incorrectly, but my understanding is that data sets still only make calculations on a row level, then averaging them up, creating an unweighted average.

Am I wrong?

[BérangèreL]

Hi @enorberg,

Thank you for reaching out to the Community!

First, I'd like to share information about the subject:

- Create calculation properties
- Using calculated properties

I also wanted to invite a couple of subject matter experts to this conversation: Hi @franksteiner79, @danmoyle and @louischausse do you have suggestions to help @enorberg, please?

If anybody else has anything to add and/or share, please feel free to join in the conversation 🙂

Thank you very much 🌟 and have a great day!

Best,
Bérangère