Need help with DAX formula

[ModelFear]

Dear community, 

I post this question as a fresh question if it is ok. 

Below is my Sales table of various dishes :

Below is my table called Recipe, where Subtotal is the cost to make each Dish:

Below is my model. I created a bridge table to avoid many to many relationship of Sales and Recipe tables:

I am trying to calculate margin for each dish using formula of (Selling price - Subtotal/Selling price). But obviously newbie here cannot DAX very well. 

Thank you in advance for your kind help!

[halfglassdarkly · Accepted solution]

Note LOOKUPVALUE wouldn't work here either because there are multiple records per [Dish ID] in the Recipe table, so some form of aggregation is required. I've opted to sum 'Recipe'[Cost] but you could also use:

Margin = 

VAR RecipeCost = Calculate(Max('Recipe'[Subtotal]),

'Recipe'[Dish ID]=EARLIER('Sales'[Dish ID]))

RETURN

'Sales'[Selling Price per kg] - RecipeCost

Also note calculations like this as well as the Subtotal field in 'Recipe' would be good candidates for creating measures rather than calculated columns.

[ModelFear]

Thank you so so so so much halfglassdarkly. I have been working on this table as a simple manufacturing model to take on harder manufacturing model. I cannot say how little we all know about dax formulas etc even after attending power bi courses. I tried tried to use chatgpt etc etc to no avail. This just shows that coding, programming is still important as the nuance of each model requirement is different. Thank you and so grateful. Yours sincerely, Modelfear (Lin) 

[halfglassdarkly]

As Manvishah17 has said, Related isn't going to work here. Try using something like:

Margin = 

VAR RecipeCost = Calculate(Sum('Recipe'[Cost]),

'Recipe'[Dish ID]=EARLIER('Sales'[Dish ID]))

RETURN

'Sales'[Selling Price per kg] - RecipeCost

[danextian]

Hi @ModelFear ,

The name of the fact tables are covered.  That aside, you can't use RELATED with the way your relationships are set up. RELATED works on the many side of a one to many or many to one relationship.  Both Sales and Recipe are on the many side and relationships do not flow from of any these tables to the other. They're only bridged by Bridge DishID. RELATED will not function either if used on a many to many relationship -  between Sales and Recipe, out of the many rows for say Dish ID 3001 in Recipe, which one should Sales pick? Try using LOOKUPVALUE instead.

[manvishah17]

Hi @ModelFear , 
RELATED Dax can be use when there is direct relation btw two tables.

Refer this for more information.

https://learn.microsoft.com/en-us/dax/related-function-dax