Forum Discussion

kingchad5's avatar
kingchad5
Helper I
8 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
    8 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.

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

Hi kingchad5,

I got and download it. All thing is good. It seems there are blank rows in your resource table. I have no power to edit your query as follows.



Please click Query Editor, and click remove rows->remove blank rows, and check if it works fine.

Best Regards,
Angelia

kingchad5's avatar
kingchad5
Helper I
8 years ago

This has been resloved.  The blank error I was getting was a related to the column itself being corrupt or something.  I created a new measure and it worked.  

 

Thanks for your help,

 

Chad