Set Analysis using formula

[ksharpes]

Hello.

I have the following formula to work out the Customer that has the lowest price "FirstSortedValue(DISTINCT Customer , [Unit Price])"

Now I would like to work out how much that Customer sold for the product in question.

I thought it would look something like 

Sum({<Customer ={'=FirstSortedValue(DISTINCT Customer , [Unit Price])'}>} SALES)

I've tried single and Double quotes, with or without "=" but its not working any suggestions?

[lironbaram]

hi 

try this formula 

Sum({<Customer ={"$(=FirstSortedValue(DISTINCT Customer , [Unit Price]))"}>}[Sales Amount])

[ksharpes]

So i think i may have missed out an important bit of information as it isn't giving me the result I was looking for.

I have am looking for the lowest price for a particular product field name is called [Common Name] 

FirstSortedValue(DISTINCT BU , [Unit Price])

The above formula does give me the right answer however if i put it within a formula "Sum({<Customer ={"$(=FirstSortedValue(DISTINCT Customer , [Unit Price]))"}>}SALES)" if then gives me the Customer with the lowest price overall instead of that particular Product so it sums the sales for that customer and not the customer with the lowest price for that product.

[sunny_talwar]

May be this

Sum(Aggr(
    If([Unit Price] = Min(TOTAL <Product> [Unit Price]), Sum([Sales Amount]))
, Product, Customer))

[ksharpes]

No that didn't work

[sunny_talwar]

Would you be able to share a sample or sample data and the expected output to help you better?

[Rohan]

Hello, can you try this formula & check if this works for you ?

min({<Customer ={"$(=FirstSortedValue(DISTINCT Customer , [Unit Price]))"}>}[Unit Price])

[ksharpes]

Sample data below

There would be multiple products:

CustomerProduct CodeProduct TypeCommon NameQUANTITYSALES Unit Price 

Cust 1	abc1	1	Product 1	72	111.5	€           1.55
Cust 1		1	Product 1	36	55.75	€           1.55
Cust 1	abc1	1	Product 1	432	669	€           1.55
Cust 1	abc1	1	Product 1	72	111.5	€           1.55
Cust 1	abc1	1	Product 1	36	55.75	€           1.55
Cust 1	abc1	1	Product 1	72	111.5	€           1.55
Cust 1	abc1	1	Product 1	36	56.93	€           1.58
Cust 1	abc1	1	Product 1	648	1024.74	€           1.58
Cust 1	abc1	1	Product 1	36	56.93	€           1.58
Cust 2	abc1	1	Product 2	36	59.04	€           1.64

I would like to show something like the below (the Sales of min price seller formula is what I'm having trouble with)

Common product name	Total Product sales	Customer with lowest min price	Sales of min price seller	Customer with lowest avg price	Sales of avg price seller
Product 1	2,313 €	Cust 1	2,254 €	Cust 2	2,254 €
Product 2	53,983 €	Cust 2	59 €	Cust 1	59 €
Product 3	14,788 €	Cust 2	10,884 €	Cust 1	10,884 €

[lironbaram · Accepted solution]

hi 

because you need to find the lowest customer for several products , you can't use set analysis 

set analysis returns one value for the whole app, and not a value per row 

this is a formula to find the customer with the min price for each product 

FirstSortedValue(aggr(Customer,Customer,CommonName),aggr(minstring(UnitPrice),Customer,CommonName))

you need to adjust the field names for your data 

this the amount the customer above has purchased 

FirstSortedValue(aggr(sum(SALES),Customer,CommonName),aggr(minstring(UnitPrice),Customer,CommonName))

[ksharpes]

Thanks for the solution, also thanks for the explanation, I was unaware of that limitation with Set Analysis.