Multiplying values from different tabel

[Molin]

Hi, 

I am working on a report where I want to calculate theoretical usage for different ingredients used in production, based on sales numbers. 

Based on the model below, I have tried the following measure: 
" Theoretical Usage =

Sumx(values(MenuComponent[MenuItem]),Sum(IngredientComponent[Amount])*sum(CheckDetails[SalesCount])) " 

The formula works great when I filter down on a single MenuItem, however when there are two or more MenuItems, the value is far to high. In excel I would use a Sumif or Lookup function and I have previously used Related in Power BI, but thats donts seem possible in this case. Is there a way to get the total usage for each ingredients based on all sales count?

Thanks in advance.  

[amitchandak]

@Molin , Try like

Sumx(values(MenuComponent[MenuItem]),Max(IngredientComponent[Amount])*sum(CheckDetails[SalesCount]))

[AllisonKennedy]

@Molin I'm not sure what you're asking, can you provide sample numbers of expected values and what the SUMX is giving? Your measure looks to be on the right track, not sure if you are looking more for an AVERAGEX?