return max cost of product

[thanhphongle]

Hello Community,

this is my table

Product	Cost
A	10
B	20
C	5
D	15
B	20
A	10
A	10
C	5
A	10
D	15

As you can see:

sum(Product A) = 40

sum(Product B) = 40

sum(Product C) = 10

sum(Product D) = 30

I want to create a textbox which returns the max sum(Cost). In this case it is A and B. Could anyone help me out how I can return both values?

It should return A = 40 and B = 40

[Gysbert_Wassenaar]

Try this: =concat(  aggr(only({<Product_={'=rank(sum(Cost))=1'}>}Product_) & ' = ' & sum({<Product_={'=rank(sum(Cost))=1'}>}Cost), Product_), ' and ')

[sunny_talwar · Accepted solution]

May be this:

=SubField(Concat(DISTINCT {<Product = {"=Aggr(Sum(Cost), Product) = Max(TOTAL Aggr(Sum(Cost), Product))"}>} Product, ','), ',', 1) & ' = '

& Max(TOTAL Aggr(Sum(Cost), Product))

& ' and ' &

SubField(Concat(DISTINCT {<Product = {"=Aggr(Sum(Cost), Product) = Max(TOTAL Aggr(Sum(Cost), Product))"}>} Product, ','), ',', 2) & ' = '

& Max(TOTAL Aggr(Sum(Cost), Product))

=Concat(DISTINCT {<Product = {"=Aggr(Sum(Cost), Product) = Max(TOTAL Aggr(Sum(Cost), Product))"}>} Product, ', ') & ' with a total sale of '

& Max(TOTAL Aggr(Sum(Cost), Product))

[sinanozdemir]

Please see the attached.