Identifying the group with the max value

[#1]

Hi,

i have teh following table:

Group         Sales

Sports        50

Sports1      100

sports2       30

i have 2 text objects in which i have 2 show the group with the maximum sales and the sales for that particular group

Maximum sales can be obtained using the below expression:

=Num(Max(Aggr(Sum(Sales), Group)), '###,##0')

what would be the expression for finding its corresponding group (in this case sports1)

thanks

[#2 · Accepted solution]

Hi Yousuf

Is this what you need?

=Only({<Sales = {"$(=Max(Sales))"}>} Group)

Lukasz

[#3]

Hi,

There would be FirstSortedValue but it would return null if more than 1 group had achieved the max sales.

You can obtain a concatenation of all the winning groups like this:

=Concat({<Group={"=Sum(Sales)=Max(TOTAL Aggr(Sum(Sales), Group))"}>} DISTINCT Group,', ')

Winning groups are separated by ', ' only if there is more than 1. This formula would also work without sales being summed by group in the table.

Hope this helps.

[#4]

Thanks Likasz

[#5]

Thanks pdecalan