Forum Discussion

kingchad5's avatar
kingchad5
Helper 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
    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,

First, in Query Editor navigator, remove all blank row by click remove rows->all blank rows as follows. And create the calculated column and check if it works fine.



>>how would I handle the calculation if in Table 2 the quote does not have an associated Opportunity ID?

We get the Min price based on Table2[Opportunity ID], I have edit my reply and unload the download again. 

Then, you data structure is same with mine. If this still doesn't resolve your issue, could you please share your .pbix file for further analysis?

Best Regards,
Angelia

kingchad5's avatar
kingchad5
Helper I
8 years ago

I made a little bit of progress.  I am now getting into a situation that if there are more than one quote with the same price it errors because it is expecting a single value.  You can reproduce the error by changing quote ID ABC124 to 100 for the quote price.

 

I would like to take the newest quote date if there are more than one.   The column that has the date is called [Created].

 

thanks,

 

Chad 

  • 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