Ranked Fields in a Straight TABLE

[#1]

I have the following data:

Customer, Products

In my straight table I have Customers going down the left and Products A, B, C will be across the top (I don't want to use a pivot table).

Is there an expression I can write that when I click on a SALESPERSON, the FIRST column will populate with the PRODUCT sold most by that sales person, then the next product, etc.

For example.

I will click John (Salesperson) and the straight table will show:

PRODUCT B PRODUCT A PRODUCT C
CLIENT ZZZ 500 400 300

then....

I will click Jane (Salesperson) and the straight table will show:

PRODUCT C PRODUCT B PRODUCT A
CLIENT ZZZ 150 125 75

I would use a pivot table and sort by size, but I need to have multiple expressions. I need to have all of this data in 1 object since I will be exporting to the report function and will cycle through dozens of salespeople.

Thanks in advance for any help!

[pover · Accepted solution]

Make the label of the 1st expression be

=only({$<Product={"=rank(sum(Sales),4,1)=1"}>} Product)

and the expression would be

sum({$<Product={"=rank(sum(Sales),4,1)=1"}>} Sales)

and then for the second, third, etc. columns you just change the rank to equal 2,3, etc.

Regards.

[#3]

Thanks!!! This worked PERFECTLY!