Forum Discussion
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!
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
2 Replies
- Vvelarde
Community Champion
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
Wow, that's easy and that looks like what I was up to. Thanks.
