FirstSortedValue Function with Aggr

[ishanbhatt]

Hello All,

Using FirstSortedValue and Aggr functions we can found the specific string from our data set.

Let's start with a simple example.

Sales:

Country	Sales
US	2000
China	1500
India	2500
Japan	1000
Canada	4000

Now I want to found which country has highest sales? For this, We can use FirstSortedValue with Aggr function.

!

=FirstSortedValue([Country],-aggr(Sum([Sales]),[Country]),1)

Let's understand the expression: 

1. Aggr(Sum(Sales), Country): Finding Sum of Sales aggregated by Country.
2. FirstSortedValue([Country], -aggr(Sum9Sales),[Country]),1): Now We need the country name based on higest sales. So, We've used Country field in FirstSortedValue function.
   • Minus(-) Sign: if we did not put minus sign then upper expression give the lowest sales country name.
   • Number (rank): put your desired number rank in the last.

So Expression gives the answer: Canada

Some More Example:

1. FirstSortedValue([Country],-aggr(Sum([Sales]),[Country]),2)

           Answer : India

2. FirstSortedValue([Country], aggr(Sum([Sales]),[Country]),1)  //Remove Minus sign

          Answer: Japan

3. FirstSortedValue([Country], aggr(Sum([Sales]),[Country]),2)  //Remove Minus sign

          Answer: China

[jgreen95]

it possible to return the actual value as well as the Country? 

Once i know the country with the top sales i want to know the value of that as well.

[lakhansajwan]

IN this example you dont need to do the Aggr inside the FirstSortedValue function as we have single sales value for a country for this example even if we pass sales dimension instead of Aggr function it will give us the correct result as desired.

You would require the Aggr function when we are having multiples vales of sales for a single country and then you wanted to do the group by Sales  Country Wise, as this will give me single sum value for each country.

Hope this clear the things more 

[Almen]

@jgreen95 

Just use Max([ValueField]) or if you have multiple values MAX(AGGR(SUM([ValueField]), [GroupField])).