First sorted value

[jamelmessaoud]

Hi guys,

Any idea why this will not work?

=firstsortedvalue(Sum({<[Sub Contractor Type] = {'Drop Rate'}>}[Sub Contractor Rate]), Aggr(Sum({<[Sub Contractor Type] = {'Base Rate'}>}[Sub Contractor Rate]),[Sub Contractor]))

This is driving me mad!

Thanks

J

[sunny_talwar]

Because FirstSortedValue and Sum are both Aggregation function and when they have to be used together need Aggr() function between them.....

[sunny_talwar]

What exactly are you trying to do here?

[jamelmessaoud]

I see.  Me being a bit dim then!

I will do a bit more messing around to try and find a solution.

Thanks as always Sunny

[itec_pao]

Hi all,

The use of firstsortedValue() with aggr would be like:

FirstSortedValue(Dimension, -Aggr(Expression, Dimension, ChartDimension)

firstsortedvalue([Sub Contractor], -Aggr(Sum({<[Sub Contractor Type] = {'Base Rate'}>}[Sub Contractor Rate]),[Sub Contractor]))

(The - sign is for descending, so that you get the top/best ones first)

[jamelmessaoud]

Hi Oktay,

I would like to show the lowest Drop Rate instead of the Sub Contractor.

The drop rate is a calculation which is:

Sum({<[Sub Contractor Type] = {'DropRate'}>}[Sub Contractor Rate])

So i am guessing what I want to achieve is not possible by firstsortedvalue function?

Thanks

[itec_pao · Accepted solution]

Hi Jamel,

What is your chart dimension? If it is Sub Contractor, then min({<[Sub Contractor Type] = {'DropRate'}>}[Sub Contractor Rate]) would be sufficient. However, having different dimensions additionally, then firstsortedValue() is the one you need.

What is exactly what you try to calculate? Dimension and Expressions please

Regards,

OKtay

[sunny_talwar]

May be just this then

Min(Aggr(Sum({<[Sub Contractor Type] = {'DropRate'}>}[Sub Contractor Rate]), [Sub Contractor]))

[jamelmessaoud]

Both of your solutions did exactly what i wanted

It is a shame I cannot set both to correct!

Thanks very much for your help guys

J