Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
Try this
Sum(Aggr(
If(
FirstSortedValue(DISTINCT TOTAL <[Product Name]> Seller, Price) = Seller,
Sum(Sales)
)
, Seller, [Product Name]))
Try this
FirstSortedValue(Seller, Price)
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?
Set analysis will not work... may be try this
Sum(Aggr(If(FirstSortedValue(TOTAL <BU> DISTINCT BU, Price) = BU, Sum(Sales)), BU, Invoice))
What field should "Invoice" be?
I don't really know your data... but the idea is that a field that tells us how many items sold by a seller
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.
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.
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 |
Try this
Sum(Aggr(
If(
FirstSortedValue(DISTINCT TOTAL <[Product Name]> Seller, Price) = Seller,
Sum(Sales)
)
, Seller, [Product Name]))