Skip to main content

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.

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.

 

 

 

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.

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

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.


Brilliant. THANK YOU.

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


Thank you! 

Reply


Terms & Conditions