Problem with my 'firstsortedvalue' expression, Thanks!

[#1]

Hello everyone!

I succeeded with Highest and lowest price to get the country name and MonthYear, but using the same expression, I can't get it for for my highest export in $ as show in the orange box below the 3,576,515 $ :

Here's my expressions for the Country:

     firstsortedvalue (country, -Aggr(Max[ExpVal],country))

     // ExpVal = Export Value in US$

     And my expressions for the MonthYear (MM-YYYY):

     firstsortedvalue (MonthYear, -Aggr(Max[ExpVal],MonthYear))

     // ExpVal = Export Value in US$

I also tried with the expression:

only(if(aggr(Rank(Sum([Sales Amt])),Salesperson)=1,Salesperson))

But I get the same empty result.

Thanks for you collaboration!

[#2 · Accepted solution]

Hi,

I would use:

=firstsortedvalue (country, -Aggr(sum[ExpVal],country))

=firstsortedvalue (MonthYear, -Aggr(sum[ExpVal],country))

If this still returns null ,it may be the case that you have more than one first value. You can use this solution adapted from cleveranjos

=FirstSortedValue(country,-Aggr(Sum([ExpVal])+rank(country,country)/10,country))

=FirstSortedValue(MonthYear,-Aggr(Sum([ExpVal])+rank(country,country)/10,country))

This will use the alphabetical order (of the country name) to be your second sort.

I hope this helps.

Kind regards,

Nuno

[#3]

Thanks Nuno!

The second expression type

=FirstSortedValue(country,-Aggr(Sum([ExpVal])+rank(country,country)/10,country))

is working just fine.

Thanks again Nuno!