cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Katerina_e Frequent Visitor
Frequent Visitor

Retrieve value for the corresponding MIN value in other column

Hello!

 

I have been trying to figure out that for a while and now I realize I might need some help.

 

To simplify, I have a table similar to this.

 

CustomerYearPriceQuantity
A140100
A15010
A234121
A23512
B1452
B1401


I want to create a measure that would return corresponsing Quantity for the Row with the minimum price.

 

Something like this.

 

CustomerYearMin PriceQuantity
A140100
A234121
B1401

 

 

It is very easy to work with a measure of MIN ([Price]), but how to "VLOOKUP" quantity to it? I tried a lot of combinations of CALCULATE + FILTER, but it all gives a wrong result. I think I should use more of FILTER functions in the formula.

 

I do not want to create a duplicate table, because my current table is already big and I just feel I can do it with a measure..

 

Thanks!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Retrieve value for the corresponding MIN value in other column

@Katerina_e

 

Hi, try with this:

 

MinPrice = MIN(Table1[Price])

 

Quantity of MinPrice =
VAR PRI = [MinPrice]
RETURN
    CALCULATE ( SUM ( Table1[Quantity] ); FILTER ( Table1; Table1[Price] = PRI ) )

Regards

Victor




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




2 REPLIES 2
Super User
Super User

Re: Retrieve value for the corresponding MIN value in other column

@Katerina_e

 

Hi, try with this:

 

MinPrice = MIN(Table1[Price])

 

Quantity of MinPrice =
VAR PRI = [MinPrice]
RETURN
    CALCULATE ( SUM ( Table1[Quantity] ); FILTER ( Table1; Table1[Price] = PRI ) )

Regards

Victor




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Katerina_e Frequent Visitor
Frequent Visitor

Re: Retrieve value for the corresponding MIN value in other column

Wow, that's easy and that looks like what I was up to. Thanks.