Returning seller name where selling price is the lowest.

[ksharpes]

Basically I would like to replicate the below table based on the sample data below it.

For Each Product I would like to show the seller name for the lowest price

Using the following expression only shows me the seller for the lowest price across all products, instead of the lowest seller for each product. 

Only({< Price = {$(=Min(Price))} >} Seller)

Product Name	Seller with Lowest Price
Product 1	Seller A
Product 2	Seller A
Product 3	Seller C
Product 4	Seller C

Product Name	Price	Seller
Product 1	1.1	Seller A
Product 1	1.2	Seller B
Product 1	1.3	Seller C
Product 2	1.1	Seller A
Product 2	1.2	Seller B
Product 2	1.3	Seller C
Product 3	1.1	Seller A
Product 3	1.2	Seller B
Product 3	1.0	Seller C
Product 4	1.1	Seller A
Product 4	1.2	Seller B
Product 4	1.0	Seller C

[sunny_talwar]

Try this

FirstSortedValue(Seller, Price)

[ksharpes]

Thanks looks like i needed to add in DISTINCT as sometimes the lowest value was the same across the same seller.

So the below does it.

FirstSortedValue(DISTINCT BU, Price)

If I want to add this value into a set expression how would i do that?

eg, if for the lowest seller of the product, how much are the total sales that seller has sold for that product?

[sunny_talwar]

Set analysis will not work... may be try this

Sum(Aggr(If(FirstSortedValue(TOTAL <BU> DISTINCT BU, Price) = BU, Sum(Sales)), BU, Invoice))

[ksharpes]

What field should "Invoice" be?

[sunny_talwar]

I don't really know your data... but the idea is that a field that tells us how many items sold by a seller

[ksharpes]

Ok I'm a little lost.

I have a field called "SALES" and I  want to get the total of this field for the product, for the Seller with the lowest sale price.

[sunny_talwar]

Can you share a sample data and output you expect to see from it... because I am equally lost here... without knowing what exactly you have... I am trying to give you a very generic solution... but if you want specifics, provide a better sample data just like you did initially.

[ksharpes]

It would be like the below.

The Sales column would be to show how much the total sales was for the seller with the lowest price.

If i also wanted to do this based on the lowest Average price, would this be possible?

Product Name	Seller with Lowest Price	Sales
Product 1	Seller A	3450
Product 2	Seller C	2350

Product 1	1.1	Seller A	1100
Product 1	1.15	Seller A	1150
Product 1	1.2	Seller A	1200
Product 1	1.2	Seller B	1200
Product 1	1.25	Seller B	1250
Product 1	1.3	Seller B	1300
Product 1	1.3	Seller C	1300
Product 1	1.35	Seller C	1350
Product 1	1.4	Seller C	1400
Product 2	1.1	Seller A	1100
Product 2	1.15	Seller A	1150
Product 2	1.2	Seller B	1200
Product 2	1.25	Seller B	1250
Product 2	1.0	Seller C	1000
Product 2	1.35	Seller C	1350

[sunny_talwar · Accepted solution]

Try this

Sum(Aggr(
  If(
    FirstSortedValue(DISTINCT TOTAL <[Product Name]> Seller, Price) = Seller, 
    Sum(Sales)
  )
, Seller, [Product Name]))