Max of both values

[Fab62400]

Hello,

I've a table like this :

I have a table like this. I would like to find the max of "Amount" and also the corresponding "Team" data.
For example :
for the date of 01/08/2013:
the max of Amount is 25000 and the Team is "Alpha".
I know how to find the maximum of Amount but not the Team column with set analisys.

=max ({<[SalesGroup]= {'East'}>} VALUE,1)

I would like to display both values.

Thank for your help !

[martinpohl · Accepted solution]

you need something like

MaxString( if( aggr(nodistinct  max(Amount), Date) = Amount, Team, null()))

Regards

[tresesco · Accepted solution]

May be a simpler one for Team value like:

=FirstSortedValue ({<[SalesGroup]= {'East'}>} Team, -Amount)

[Fab62400]

Thanks it works!

Is it possible to have the 3 max text value  like the   function max(.., 1) max(.., 2) max(.., 3 ) ?

[sunny_talwar · Accepted solution]

Just like Max, FirstSortedValue can also show text related to 2nd and 3rd max value

=FirstSortedValue ({<[SalesGroup]= {'East'}>} Team, -Amount, 1)
=FirstSortedValue ({<[SalesGroup]= {'East'}>} Team, -Amount, 2)
=FirstSortedValue ({<[SalesGroup]= {'East'}>} Team, -Amount, 3)

[Fab62400]

Yess !

Thank you !