Forum Discussion

ModelFear's avatar
ModelFear
Frequent Visitor
2 years ago
Solved

Need help with DAX formula

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...
Need Help
  • halfglassdarkly's avatar
    halfglassdarkly
    2 years ago

    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.

halfglassdarkly's avatar
halfglassdarkly
Responsive Resident
2 years ago

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's avatar
    ModelFear
    Frequent Visitor
    2 years ago

    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)