Fetching Value of Make having maximum Invoice Price for each Type

[mahesh_agrawal]

Hi Folks,

I am trying to find out the value of "Make" field which has highest "Invoice" value for each "Type".

I have achieved it in the backend, but to make it more dynamic based on front end selection, I need to calculate it at UI level.

Example of Source Data: (NOTE: Full excel file for the same is attached)

Type	Make	Invoice
Hybrid	Honda	17911
Hybrid	Honda	18451
Hybrid	Toyota	18926
Wagon	Audi	37060
Wagon	Audi	44446
Wagon	BMW	30110
Wagon	Chevrolet	20394
Wagon	Chrysler	28725

Target Output for above partial data:

Type	MAX(Invoice)	Max Price maker
Hybrid	18926	Toyota
Wagon	44446	Audi

Thanks in advance for the help.

Cheers,

Mahesh

[Chanty4u]

try

FirstSortedValue(  Type, -Invoce)

[sunny_talwar · Accepted solution]

Try this

=FirstSortedValue(Make, -Invoice)

[mahesh_agrawal]

Thanks Sunny.

Apart from this, is there any way we can achieve the same using Nested aggregation with MAX() and AGGR().

You solution is quite simple and awesome. I am just trying to expand my knowledge on use of AGGR() function too.

Thanks again for the help and look forward to your reply.

[sunny_talwar]

Sure thing... try this

=Concat(Aggr(If(Max(TOTAL <Type> Invoice) = Invoice, Make), Type, Make, Invoice))

[mahesh_agrawal]

Thanks a lot GURU.