top 3 values and corresponding attributes

[#1]

Product	Source	Destination	Cost	Cost 2	Total Cost
Handbag	A	A1	0	5	5
Handbag2	B	B1	10	10	20
Handbag3	C	C1	20	15	35
Handbag4	D	D1	30	10	40
Handbag5	E	E1	40	20	60

Right now, I'm using the above example where I'm using =FirstSortedValue(Source, [Total Cost],1)

Is there any way that if my data had 0 as one of the cost, not to include it as the cheapest source?

For example, the cheapest source would be B, C, and D.

[Anonymous]

=IF(FirstSortedValue(Source, [Cost],1) = 0 or FirstSortedValue(Source, [Cost2],1) = 0, FirstSortedValue(Source, [Total Cost],2), FirstSortedValue(Source, [Total Cost],1))

[#3]

This doesn't seem to work properly... it gives me the same value as the =FirstSortedValue(Source, [Total Cost],1)) even though the cost2 is 0. Any other ideas?

[Anonymous]

IF(FirstSortedValue(aggr(Sum(Cost),Source),Cost) = 0 or FirstSortedValue(aggr(Sum([Cost 2]),Source),[Cost 2]) = 0,

  FirstSortedValue(aggr(Sum([Total Cost]),Source),[Total Cost],2),FirstSortedValue(aggr(Sum([Total Cost]),Source),[Total Cost]))

[#5]

This gives me the value. What do I do to get the field associated with that value? For example, instead of 20 in your example. I should see Handbag2.

[Anonymous]

IF(FirstSortedValue(Cost,aggr(Sum(Cost),Source)) = 0 or FirstSortedValue([Cost 2],aggr(Sum([Cost 2]),Source),[Cost 2]) = 0,

  FirstSortedValue(Product,aggr(Sum([Total Cost]),Product),2),FirstSortedValue(Product,aggr(Sum([Total Cost]),Product)))

[simenkg]

Firstsortedvalue takes set analysis.

How about:

=FirstSortedValue({$<Cost={">0"}>}Source, [Total Cost],1)

Regards
SKG

[Anonymous · Accepted solution]

Based on Simen's suggestion this is more straightforward.

=FirstSortedValue({<Cost = {">0"},[Cost 2] = {">0"}>}Product, [Total Cost])

[#9]

=FirstSortedValue({<Cost = {">0"},[Cost 2] = {">0"}>}Product, [Total Cost],1) seems to do the trick.

Now how do I find the Cost and Cost 2 associated with that? What is the right calculation to get the Cost and Cost 2?

From Calculation above:

cheapest:

Product, Source, Destination, Cost, Cost 2, Total Cost

Handbag2, B1, 10, 10, 20

[Anonymous]

=FirstSortedValue({<Cost = {">0"},[Cost 2] = {">0"}>}Product, [Total Cost])

Replace Product in bold with what field you want to grab.  You Don't need the 1 at the end because it is implied if left out.