Need a formula to show highest performing employee

Hey Airtable Community! I’m currently trying to create a formula that can show me the highest performing employee at any given time based upon the total revenue produced by said employee.

I currently have a Table within my Workspace titled “Employees” where I’m able to sort by the highest-performing employee by using a rollup from my “Customer Orders” Table. However, there is a table called “Clubs” where I want to be able to show the highest-performing employee in a separate field driven by a formula that reads a rollup of Employee-orders. Please refer to the photos below for more context:

As you can see, each order placed within the club deposits to the “Clubs” table within the “Customers” linked records, along with a Lookup field showing the name of each employee responsible for each sale. I want to write a formula that counts the number of instances of each employee and finds the MAX string (in this case, “CJ” since he has the most orders for Club #2, and places the name in the field next to it.

If anyone could help me out, I’d really appreciate it!!

Customers table: get a Count field of each linked Order.

Clubs table: get a Rollup field that is the MAX(values) of the Count field from above.

Customers Table: get a Lookup field that brings in that^ Rollup. Get a formula field that’s IF({Lookup of Rollup} = {Count field}, "yes").

Clubs table: get a Lookup field with the condition "Show Team Member where Formula Field = “yes”

1 Like

Look at this thread; you’ll find a script that might solve your problem!.. :slight_smile: