cancel
Showing results for
Did you mean:
Highlighted
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.

 Customer Year Price Quantity A 1 40 100 A 1 50 10 A 2 34 121 A 2 35 12 B 1 45 2 B 1 40 1

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

Something like this.

 Customer Year Min Price Quantity A 1 40 100 A 2 34 121 B 1 40 1

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

## 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

Proud to be a Datanaut!

2 REPLIES 2
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