Need to find the largest number in a column for every player with unique ID
I have 2 columns: Player Id, Sponsor Id.
Same players can have multiple sponsor ids. For example, player 1 has sponsor Ids 1, 2, 3, 4, 5 etc.
What I want to do is to find the largest sponsor id for every player, and then make a unique count of total player having only 1 or only 2 or only 3 etc sponsor ids.
For example, In case of player 1, the largest number is 5, so it should only fall in that category of 5 and must not be included in unique counts of 1,2,3 or 4.
I am trying to use this custom expression but its not working:
NthLargest(([Sponsor ID]) over ([Player ID]))