Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Katerina_e
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
Vvelarde
Community Champion
Community Champion

@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

View solution in original post

2 REPLIES 2
Vvelarde
Community Champion
Community Champion

@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

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors