Using a calculated value to pull field name for highest value.

[joshm43]

Hello -

I am trying to identify a field value based on the result of a calculation.

In the following example, I have four locations with varying numbers of incidents for each entry. 

Location	Incidents
West	32.5
North	24.9
South	63.2
West	22.5
East	30.2
North	57.8
South	64.7
North	4.7
West	10.1
East	59
South	29.2

What I want  to do is to identify the location with the highest average number of incidents, the pull the value for that location.

I am able to use Max(), Aggr(), and Avg() to get the highest average number of incidents:

Max(Aggr(Avg(Incidents), Location))

But am then challenged to retrieve the Location value associated with it.

Thanks,

Mitch

One late addition.  My intent is to be able to use this expression in a Text object.

[Lisa_P · Accepted solution]

The FirstSortedValue() Function is what you need.
FirstSortedValue(Location, - Aggr(Avg(Incidents), Location))
The FirstSortedValue will select from the lowest, so the minus symbol then looks for the highest.

[joshm43]

Thanks for the quick answer.  I tested and it works perfectly!  In looking at your example, I was not specifying the field correctly.  Best, Mitch