Highest Sold Product calculated by aggr value for each Customer

[mccook]

I've been going around in circles a little bit with firstsortedvalue() and aggr() to try to achieve below:

My data is a little like this:

Customer	Product	Product Type	Units	Value
Dean Ltd	ZZZ	123	10	100
Dean Ltd	XXX	345	35	350
Dean Ltd	ZZZ	123	20	200
Carl Construction	ZZZ	123	30	300
Carl Construction	XXX	345	25	250

And I need the result to be like this in my pivot table:

Customer	Product	Product Type	Value	Highest Sold Product
Dean Ltd	ZZZ	123	300	XXX
Dean Ltd	XXX	345	350	XXX
Carl Construction	ZZZ	123	300	ZZZ
Carl Construction	XXX	345	250	ZZZ

It's the highest sold product field I would appreciate any help with please?

Thanks,

Dean

[Anonymous]

it was a hard way to find (maybe) the solution. At least with your data I am able to produce your desired output table

I used firstsortedvalue for your desired column

= FirstSortedValue(total <Company> Product, -Val)

I hope that this works not oly with your 5 rows of data. Please try!

[sunny_talwar]

You can try this

Concat(DISTINCT TOTAL <Customer> Aggr(
    If(Sum(Value) = Max(TOTAL <Customer> Aggr(Sum(Value), Customer, Product)),  Product)
, Customer, Product))

[sunny_talwar]

I think this was only by chance that this worked. If you change the Value for first row to 151 from 100.. you will see that the expression that you have mentioned will continue to show XXX as the most valuable product, but now ZZZ is 351. So, it should be ZZZ as most valuable product for Customer Dean Ltd based on the new data.

Customer	Product	Product Type	Units	Value
Dean Ltd	ZZZ	123	10	151
Dean Ltd	XXX	345	35	350
Dean Ltd	ZZZ	123	20	200
Carl Construction	ZZZ	123	30	300
Carl Construction	XXX	345	25	250

[Anonymous · Accepted solution]

Okay, I misunderstood that you are looking for highest value, not for highest sum

Now i changed the expression and made some tests and for me it looks good

can you make further tests?

= FirstSortedValue(total <Company> Product, -aggr(sum( total <Company,Product> Val),Company,Product))

[mccook]

Thanks, this was close, it works for like 95% of my data

[mccook]

Thanks, this seems to work perfectly.