Hi there,
I am dealing with a data calculation situation that I just can't seem to solve. We are in the business of staffing/recruitment and I need to calculate the total revenue being brought in by a team member on all jobs they are associated with.
The scenario: The team members are assigned to a "Search", at which point they work with the client to fill an open role - these searches have Unique IDs. We bill the client in installments, anywhere between 2 - 6 times, but this varies search by search. In our system we record a 1st Bill Amount, 1st Bill Date, 2nd Bill Amount, 2nd Bill Date, 3rd Bill Amount, 3rd Bill Date, etc. - all the way through 6th Bill. These are all individual columns in my data table. Beyond that, the team member can be working in any of 4 roles with regard to a single search being worked: Search Leader 1, Search Leader 2, Opportunity Owner 1, Opportunity Owner 2. These are also columns in the table. Where things get confusing is that technically an employee could be listed in more than 1 role, so for example someone could be a Search Leader 1 and an Opportunity Owner 2. In this instance, I need to be able to calculate the revenue brought in by this person, but not have it duplicated because their name is listed twice. Please find an example data table below.
My ultimate goal is to be able to report on how much revenue is billed in each month, but be able to slice that by the employee without counting double revenue where they are listed under 2 different roles.
| Search ID | Search Title | Client | Search Leader 1 | Search Leader 2 | Opportunity Owner 1 | Opportunity Owner 2 | 1st Bill Amount | 1st Bill Date | 2nd Bill Amount | 2nd Bill Date | 3rd Bill Amount | 3rd Bill Date | 4th Bill Amount | 4th Bill Date | 5th Bill Amount | 5th Bill Date | 6th Bill Amount | 6th Bill Date |
| 234jdw | Manager | Client A | John | Sally | John | Elizabeth | $ 1,000 | 1/3/2023 | $ 1,000 | 1/29/2023 | $ 500 | 4/3/2023 | | | | | | |
| 544kjs | Director | Client B | Wayne | Randy | Phil | | $ 3,000 | 12/4/2022 | $ 1,000 | 2/23/2023 | $ 1,000 | 3/26/2023 | $ 1,000 | 3/29/2023 | $ 1,000 | 4/12/2023 | | |
| 212duy | Manager | Client C | Sally | Jane | Jane | Joe | $ 1,000 | 3/21/2023 | $ 500 | 4/1/2023 | $ 1,000 | 4/15/2023 | $ 1,000 | 4/23/2023 | | | | |
| 783hfr | Manager | Client A | Sally | John | Phil | John | $ 750 | 2/2/2023 | $ 3,000 | 3/7/2023 | | | | | | | | |
Thanks in advance, and let me know if I can provide any additional clarification or detail.
