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,

Yes, you still can get the expected result using similar method. There must be a date column to recognize which quote is the newest in Table2. I reproduce your table using TableA and TableB.

You will create another calculated column using the formula below.

MinDate For Same Price = CALCULATE(MAX(TableB[Quote Date]),ALLEXCEPT(TableB,TableB[Opportunity ID],TableB[Quote Price]))



Then in related TableA, you can create a calculated column to get ID related to newest quote date for same price.

MinPrice and Latest Date QuoteID = CALCULATE(VALUES(TableB[Quote ID]),FILTER(CALCULATETABLE(TableB),TableB[Quote Price]=TableB[MinPrice]&&TableB[Quote Date]=TableB[MinDate For Same Price]))



Please download the attachment and review the details.

Best Regards,
Angelia

 

kingchad5's avatar
kingchad5
Helper I
8 years ago

I sent you a private message.

  • 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

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft 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