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.

danextian's avatar
danextian
Super User
2 years ago

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.