Combination of properties for new calculation

[Osvaldor]

Hi, 
I have customer stores  in different areas and as a reference I have a certain amount of customer stores lets say 3 in an area with a population of 150 000 people which gives me 150 000/3 = 50 000. It means that I need a population density of 50 000 to prospect a new customer store.

All my customers have the property State/Region and the property Store (which indicates have many stores they have in that area).

This means that I have multiple customers in the same State/Region and I need to summarize the number of stores for all customers in that area without summarizing the population.

Example:
Florida (population 24 million habitants)
Customer 1, 17 stores
Customer 2, 35 stores
Customers 3 44 stores

This should be 96 stores in total which gives me a population density of 250 00 habitants per store (24 000 000/96).

I have really tried to create a calculation property, but it does not take me all the way. 
And the follow up question, how do I create a graph from that information with the population density per store for all areas?

Thank you so much in advance!

[SamCBarth_LAIRE]

Hi! @Osvaldor 

If you want to make this calculation, you need to have this data accessible within whatever object you are working with - so if CONTACTS then you could have a workflow that maps a population value based on the state then use that in a calc property with how many stores they have - it would calculate it on a per record basis

You could create a custom formula in reporting to show this in aggregate - the population of the state would need to be in the formula itself

https://knowledge.hubspot.com/properties/create-calculation-properties

[karstenkoehler · Accepted solution]

Hi @Osvaldor,

I'm following the overall math but not where exactly you want to store the result of the calculation (96). Where should this live?

From what you're explaining, it sounds to me like you would need a separate object (ideally a custom object for the US state). You would then do the following:

1. Create custom object *
2. On that custom object record, you have a property for the population or that US state
3. You would then associate all customers to their respective state (could be achieved automatically with the workflow action "Create association")
4. You would create a custom object roll-up property that sums the number of stores on all associated customers
5. You can then create a calculation on the custom object record that divides the population (step 2) by the result of the roll-up (step 4)
6. You can then create a simple table report on this object (name + result from step 5)

The beauty is that the calculation properties will update accordingly whenever you associate new stores to a US state record. The same would happen when you update the population on the state record, of course.

* since custom object creation is Enterprise only, the custom object could also be any existing object you use for that purpose (e.g. tickets). I don't see, however, how this would be achieved with just one object and without maintaining associations as described above.

Best regards

[JWingate2]

Hi @Osvaldor , 

I do not believe this is possible with a calculation property, but rather a Custom Report. HubSpot has a BETA feature called Formula Field that you might be able to utilize and then report across multiple records rather than a Calculated Field that calculated for one.