Help

The Community will be undergoing maintenance from Friday February 21 - Friday, February 28 and will be "read only" during this time. To learn more, check out our Announcements blog post.

Re: Rollup: attributing value to multiple sources

Solved
Jump to Solution
234 0
cancel
Showing results for 
Search instead for 
Did you mean: 
raefpr
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi there,

I have a team of account executives and I'm trying to rollup the amount of revenue each person manages during a 12-month period. Revenue is generated on a per project basis. Each project has a start and end date and a monthly retainer fee, which are recorded in the project's record in my project table. I have a formula field ("Project Value") that calculates the total project value.

Screenshot 2025-02-14 at 9.14.39 AM.png

I maintain a second team member table and I link account executives to the projects they manage using a linked record field. 

Some projects are managed by two account executives (and are linked to two account executive records as a result).

The problem is that when I rollup the amount of revenue each person manages during a 12-month period, for projects that have two account executives attached, the rollup attributes 100% of the project revenue to both of the employees linked (rather than 50% to each). Can anyone point me towards a solution? Many thanks.

Screenshot 2025-02-14 at 9.22.39 AM.png

 

 

 

1 Solution

Accepted Solutions
Kenneth_Raghuna
8 - Airtable Astronomer
8 - Airtable Astronomer

Create a count field that counts how many account executives are linked to the project.

Create another field in your Projects table that calculates Project Revenue per Executive with a formula field: {Project Value} / {Number of Account Executives}.

Now instead of rolling up the {Project Value} field in {Revenue managed (12 mo)}, you'll roll up the {Project Revenue per Executive} field.

See Solution in Thread

4 Replies 4
Kenneth_Raghuna
8 - Airtable Astronomer
8 - Airtable Astronomer

Create a count field that counts how many account executives are linked to the project.

Create another field in your Projects table that calculates Project Revenue per Executive with a formula field: {Project Value} / {Number of Account Executives}.

Now instead of rolling up the {Project Value} field in {Revenue managed (12 mo)}, you'll roll up the {Project Revenue per Executive} field.

Mike_AutomaticN
10 - Mercury
10 - Mercury

Hey @raefpr!

A solution you can go for is to have a Count field on your Projects table, to get it to count the amount of Account Executives assigned to it. You can find more on Count Fields here.

Now that you have the count, you can have an additional field on your Projects table which would be Project Value per Account Executives, which would basically be your Project Value divided by the Count field.

Now, on your Account Executives table, you can rollup the Project Value per Account Executives field rather than the Project Value field. This will get the issue solved! You can find more on Rollup Fields here.

You might want to watch out for other limitations with your current setup. E.g. If an Account Executive gets assigned mid-way through a project.

If you need any help trying to find the best solution, please feel free to schedule a call using this link!

Mike, Consultant @ Automatic Nation

raefpr
5 - Automation Enthusiast
5 - Automation Enthusiast

Brilliant. THANK YOU.

raefpr
5 - Automation Enthusiast
5 - Automation Enthusiast

Thank you!