Forum Discussion

kingchad5's avatar
kingchad5
Icon for Helper I rankHelper I
9 years ago
Solved

MIN value based on common ID number

I am looking to create a calculated column that returns the quote number that has the lowest price for each Opportinity.     I have 2 tables.     -  Table 1 = Opportunities          -  Unique Op...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    9 years ago

    Hi,

     

    Try these calculated column formulas in the Opportunities table

     

    Minimum Price

     

    =MINX(RELATEDTABLE(Quotes),Quotes[Quote Price])

    Created date for minimum price

     

    =CALCULATE(MAX(Quotes[Created]),FILTER(CALCULATETABLE(Quotes),Quotes[Opportunity ID]=[Opportunity ID]&&Quotes[Quote Price]=[Minimum price]))

    Quote ID

     

    =LOOKUPVALUE(Quotes[Quote ID],Quotes[Opportunity ID],[Opportunity ID],Quotes[Quote Price],MINX(RELATEDTABLE(Quotes),Quotes[Quote Price]),Quotes[Created],[Created date for minimum price])

    This solution will work even if you have 100 for AB124

     

    Hope this helps.

kingchad5's avatar
kingchad5
Icon for Helper I rankHelper I
9 years ago

I sent you a private message.

v-huizhn-msft's avatar
v-huizhn-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Hi kingchad5,

Have you resolved your issue? If you have, welcome to share your solution or mark the right reply as answer. So that more people will benefit from here.

Best Regards,
Angelia