Finding theoretical product usage - salescount x ingredients

[Molin]

Hi, 

I need help with a setup related to food recipes and theoretical usage from sales statistics. The setup works as follow: sales counts are listed in tabel "CheckDetails". The food menu consist of both different dishes and menus. Menus consist of same dishes in a combi set (listed in tabel "MenuComponent"). The ingredients for each dish are listed in "IngredientComponent". 

I have no problems in finding the total sales for each dish, however I would like to multiply the CheckDetails[SalesCount] with IngredientComponent[Amount] to get the theoretical usage for each ingredients based on the sales mix.

I have tried:
Sumx(IngredientComponent,IngredientComponent[Amount]*related(CheckDetails[SalesCount])), however I get an error with "no relationship between the two tables". 

Do any of you have an idea to solve this issue?

Thanks in advance. 

[V-lianl-msft · Accepted solution]

Hi @Molin  ,

If there is no directly relationship between two tables, you need to use CALCULATE expression to get the corresponding data.

Try replacing the original RELATED call with the CALCULATE expression above:

theoretical product usage = SUMX( IngredientComponent,IngredientComponent[Amount]* 
CALCULATE( VALUES( CheckDetails[SalesCount] ), 
FILTER( CheckDetails, 
CheckDetails[KEY] = IngredientComponent[KEY] ) ) )

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Molin , there is no direct relation between these tables, so relation will not work you can try like

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

You need a common dimension to do that

[Molin]

Thanks alot for the quick response @amitchandak  

The proposed measure works to the extent that if I filter down on each dish it gives me the right amount or if the ingredients only appear in one dish, however in a table with all totals for ingredients the value is far to big. It seems like the it sums across the different dishes, which is why I thought Related would be appropiate. 

Do you have a solution for this?