Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
In that case use this expression for min price
Min(Total <Product> Aggr(avg(Amount/Quantity),Supplier, Product))
Celambarasan
Hi,
Image not displayed that you have attached.
Celambarasan
Hi,
Use this expression,
=If([Avg Price]=min(TOTAL <Product> [Avg Price]),[Avg Price])
Hope this will help you.
Celambarasan
Hi
try this
=aggr(min([Avg Price]) ,Product)
may it hepls you
thanks
rohit
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!
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
Find attached the images.
Hi,
Here Avg Price means?..How your calculating i mean like its from datasource or calculating in chart if so based on what?
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!!!
Hi,
In that case use this expression for min price
Min(Total <Product> Aggr(avg(Amount/Quantity),Supplier, Product))
Celambarasan