Forum Discussion
Helper IMIN 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 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
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.
11 Replies
v-huizhn-msftMicrosoft Employee
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
kingchad5Thank 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
- Ashish_Mathur
Super User
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.
- Ashish_Mathur
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.