Min value by two dimensions

[#1]

Hello,

I'm having a problem in retrieving the min value by two dimensions.

I mean I have this data structure

Product	Supplier	Avg Price
A	1	1.5
A	2	0.8
A	3	2.1
B	1	3.2
B	2	4.2
B	3	3.8
C	1	7.1
C	2	8.2
C	3	6.9

and I need to obtain for each product the supplier having the min average price, that is to say:

Product	Supplier	Avg Price
A	2	0.8
B	1	3.2
C	3	6.9

I've tryed a lot of combinations with min and aggr functions but with no results.

Can anyone help me?

Thanks in advance,

MJ

[CELAMBARASAN]

Hi,

     Image not displayed that you have attached.

Celambarasan

[CELAMBARASAN]

Hi,

     Use this expression,

     =If([Avg Price]=min(TOTAL <Product> [Avg Price]),[Avg Price])

Hope this will help you.

Celambarasan

[rohit214]

Hi

try this

=aggr(min([Avg Price]) ,Product)

may it hepls you

thanks

rohit

[#5]

Thanks you both!!

I've tryed with the expressions you mentioned and this one (=If([Avg Price]=min(TOTAL <Product> [Avg Price]),[Avg Price])) works perfectly for th example but not for my dashboard. When I include a new dimension It doesn't work.

Find attached a new example that includes Time Dimension. If I select a month the min value is correct but it is incorrect if no month is selected.

Thanks!

[CELAMBARASAN]

Hi,

     I'm sorry to say this i cann't open your qv file because i'm using personal edition. Can u give me as a image.

Celambarasan

[#7]

Find attached the images.

[CELAMBARASAN]

Hi,

     Here Avg Price means?..How your calculating i mean like its from datasource or calculating in chart if so based on what?

[#9]

I'm calculating  in the chart as 'amount/quantity'

Find attached the table i'm using.

These are the expessions for each column:

Avg Price: =avg(Amount/Quantity)

Min Price: =min(TOTAL <Producto> Amount/Quantity)

Thank you so much!!!

[CELAMBARASAN · Accepted solution]

Hi,

     In that case use this expression for min price

          Min(Total <Product> Aggr(avg(Amount/Quantity),Supplier, Product))

Celambarasan