Retrieve value for the corresponding MIN value in other column

[Katerina_e]

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!

[Vvelarde · Accepted solution]

@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

[Katerina_e]

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