Sum of Sales By Unique Primary Dimension

[Anonymous]

Trying to calculate sum of Sales by Client and Product in a Straight Chart but the requirement is to show Client only once for the highest Sales of the Product.

For example:

Client     Product     Sales

a           p1             100

c           p7              70

f            p4              65

even if Client 'a' sales for product 'p3' that exceed Clients 'c' and 'f', it should not be shown. Each Client should appear once and with the Product that has highest Sales.

Thank you.

[swuehl · Accepted solution]

So you want to show the highest Sales and the product the sales was achieved per Client?

Try a straight table chart with dimension Client and two expressions:

=FirstSortedValue( aggr(Product, Client,Product), aggr(-sum(Sales),Client, Product) )

=max( aggr( sum(Sales), Client, Product))

[Anonymous]

Hi Arsalan,

For the product, use:

Aggr( FirstSortedValue( Product, -Sales ), Client )

For the Sales, use:

Aggr( Max( Sales), Client )

The result is:

Please, see attached.

Regards

[Anonymous]

Thank you swuehl, it worked.

Can I limit this table to Clients with Top 10 highest Sales?
Not using Dimension Limits option but by adding a code in Dimension or Expression?

[Anonymous]

Hi Lucas,

Thank you that was helpful.

[swuehl]

Dimension limits would make it quite easy to achieve what you want.

But without dimension limits, you can try using rank() function. Check attached version.