Rank over Aggregation

[#1]

I am attempting to show the ranks across multiple products for a customer where only that single customer is selected

Sample data:

Customer 1	Product1	100
Customer 2	Product1	150
Customer 3	Product1	200
Customer 1	Product2	15
Customer 2	Product2	20
Customer 3	Product2	25
Customer 1	Product3	75
Customer 2	Product3	50
Customer 3	Product3	30
Customer 1	Product4	10
Customer 2	Product4	11
Customer 3	Product4	12

With a selection on customer on customer 1 ... I would want my final chart (straight table) to show

Product 1: 3, 100

Product 2: 3, 15

Product 3: 1, 75

Product 4: 3, 10

Such that the answer is the ranking for the product across all customers but only the selected customers values (rank and amount) show in the table.

The closest I can find is =Aggr(Rank(Sum({<Customer=>}Amount)),Product) but this gives me the rank for each product in my main table not the rank for the customer within each rank in its implied subtable.

Help appreciated.

[swuehl · Accepted solution]

Try

=aggr( rank(sum({<Customer=>}Value)),Product,Customer)

See also attached.

[#3]

Thanks muchly swuehl, nice and obvious. I think one of the reasons that I didn't work that out was I had that formula and then thought it wasn't working when the reason it didn't appear to be was because I didn't have a specific customer actually selected.

Thanks again.