MIN value based on common ID number

[kingchad5]

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 Opportunity ID

-  Table 2 = Quotes

         -  Each quote has a relationship to the Opportunity ID

Min Quote ID would be the calulated column.

Example Data

Table 1			Table 2
Opportunity ID	Min Quote ID		Quote ID	Quote Price	Opportunity ID
1	ABC123		ABC123	100	1
2	ABC126		ABC124	200	1
3	ABC131		ABC125	300	1
4	ABC132		ABC126	50	2
5	ABC135		ABC127	200	2
			ABC128	600	2
			ABC129	600	3
			ABC130	500	3
			ABC131	200	3
			ABC132	800	4
			ABC133	900	4
			ABC134	950	4
			ABC135	100	5
			ABC136	200	5
			ABC137	300	5

Please let me if you have any ideas.

Thanks,

Chad

[Ashish_Mathur · Accepted solution]

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.

[Ashish_Mathur]

Hi,

In the opportunities table, you may create this calculated column formula

=LOOKUPVALUE(Quotes[Quote ID],Quotes[Quote Price],MINX(RELATEDTABLE(Quotes),Quotes[Quote Price]),Quotes[Opportunity ID],[Opportunity ID])

However, this formnula will fail if i had 100 against ABC124 in the Quotes table.

[v-huizhn-msft]

Hi @kingchad5,

In Table2, please create calculated column using the formula.

MIN = CALCULATE(MIN(Table2[Quote Price]),ALLEXCEPT(Table2,Table2[Opportunity ID]))

Then create a calculted column using the formula below in Table1. You will get expected result shown in the screenshot.

Min Quote ID = CALCULATE(VALUES(Table2[Quote ID]),FILTER(CALCULATETABLE(Table2),Table2[Quote Price]=Table2[MIN]))

Please download the attachment to review the more details.

Best Regards,
Angelia

[kingchad5]

Thank you for your reply.  when I add these columns, I am getting this error:

Column '' in Table '' contains blank values and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table.

I checked all the columns in my tables that I am referencing and i do not have blank values.  Could it be referencing the CALCULATETABLE table?

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

[v-huizhn-msft]

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]

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]

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]

I sent you a private message.

[v-huizhn-msft]

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

[v-huizhn-msft]

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]

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