Announcements
cancel
Showing results for
Did you mean:
Creator II

First sorted value

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]))

Thanks

J

1 Solution

Accepted Solutions
Partner - Creator

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

8 Replies
MVP

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

MVP

What exactly are you trying to do here?

Creator II
Author

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

Partner - Creator

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)

Creator II
Author

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

Partner - Creator

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

MVP

May be just this then

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

Creator II
Author

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

Community Browser