Filtering table

[hworrall]

I need to calculate the revenue that is allocated to each person that obtains a client.  I have table that is has mutiple fields with a persons name in it.  I need a formula to calculate the revenue for each person.  I know how to calculate it, but I can't get it to filter correclty (if at all).  I have tried different ways of doing it, but nothing is working.  Here is a sample of the data (I have 13 more columns with people's name in them, some are the same, some are different):

I calculated the revenue by taking the total fees, dividing it by 2, then by the number of people in each category (onboarding or pursuit) and got these results:

The calculation works, but now I need to filter to show the results if Doe, Jane | 409 is in either of the columns, and still show the results of the other people that are on the same client.  So if I filtered the table above, I should show clients 38008 and 40179, but not 38009 AND the caluclations should still show for Smith | Brian | 270, Dell, Michael | 347, and Gunner, Amy | 591.  

I also have a separate table that has all of the employees listed in a column named EmployeeLastFirstID, but I don't see how I can join that Employee table to the one above.  There is only the employee name, id, and other employee related info in there (not a client id).

I am at a loss and am coming up on a hard deadline.  Anyone have any ideas?

[hworrall]

Hi, thanks for responding!  So let's say I need to filter the results in this table for Dell, Michael | 347.  

I would want the entire first row to show up, including the calculation for Smith, Brian and Doe, Jane.  If I were to filter by Doe, Jane, I would want the entire top two rows to show up.  Does that make sense?  

[Anonymous]

Hi @hworrall ,

I'm a little confused about your needs, Could you please explain them further? It would be good to provide a screenshot of the results you are expecting and desensitized example data.

Thanks for your efforts & time in advance.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.