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

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