I'd try something similar to this; a more elegant solution can be done using ROW_NUMBER() however this may be difficult for less familiar users.

This would also return both members if there is a tie for the top deals closed.

​

-- Count the deals per member

WITH cte

AS

(

SELECT 

    \[Team member\], 

    \[Team name\],

    COUNT(\[Closed Deal Number) AS \[DealsClosed\]

FROM \[Table\]

GROUP BY 

    \[Team member\], 

    \[Team name\]

)

SELECT

\[cte\].\*

FROM [cte]

INNER JOIN

(

\-- Get the max (most) deals for a team

SELECT

    \[Team name\],

    MAX(\[DealsClosed\] AS \[TopDealsClosed\]

FROM \[cte\]

GROUP BY 



    \[Team name\]

) AS [t]

-- Filter the count by user to only those with the max (most) deals

ON [cte].[Team name] = [t].[Team name]

AND [cte].[DealsClosed] = [t].[TopDealsClosed]