Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
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.
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
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?
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
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
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
I sent you a private message.
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
83 | |
70 | |
65 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |