Aggregation: finding the biggest customer for each product

[#1]

I have a table with invoices for a full year. Each invoice specifies the Customer, the Product and the Volume sold.

I am trying to make the following table:

• Product
• Total volume sold of the product
• Name of the largest customer  (in terms of volume purchased across all products, not just the volume of the product itself) buying the product
• Total volume purchased

Obviously, the first two are trivial, but I am struggeling with the expressions for the last 2 columns. 

Have spend hours on this, so help would be very much appreciated!!!

[#2]

I believe that the customer will be like this using product as dimension

=Aggr(if(Rank(Sum({<Client= P(Client), Product=>}SubTotal)) = 1, Client), Client)

[#3]

Federico,

Thanks for the suggestion. This helps a lot, but I don't have the final solution yet.

Using your suggestion, I have been able to make a table chart with dimension Customer. When I select a product, this table gives me the ranking of the customers from large to small and also the volume of the customers across all products (not just the product selected). See below:

Now, I want to take the next step. Rather than having the above table with dimension Customer and having to select products to find the largest customer, I would like to make a chart with dimension Product, which shows for each product the biggest customer and the Customer's volume across all products. I have tried your aggregation formula, but to get anything displayed, I need to aggregate across both products and customers. When I do this, I get the customer buying the biggest volume of the specific product rather tha the biggest overall customer who buys the particular product.

Any suggestions?

Thanks, Marc.

[#4]

The  previous formula only worked for the 1st ranked product-client, didnt do the job...

Here is a formula for a table with product as dimension.

Aggr(if(Rank(Sum({<Product=>}total <Client> SubTotal)) = 1, Client), Product, Client)

This shows the the biggest client thats buys each product.

In a minute the volume formula...

[#5 · Accepted solution]

Aggr(if(Rank(Sum({<Product=>}total <Client> SubTotal)) = 1, Sum({<Product=>}total <Client> SubTotal)), Product, Client)

[#6]

Federico,

Fantastic. Works perfect. Thanks a lot.

Marc.

[Anonymous]

Hi Federico,

don't understand wath is  SubTotal    in your formula ...

can u explain me please?

Thank

[#8]

SubTotal is whatever he is using, could be Qty, sales, anything. In this case I believe it should be volume.