Forum Discussion
CONDITIONAL MULTIPLICATION ACROSS 2 TABELS
Hi,
I am an amateur in Power BI but need to do something fairly complex.
I need to derive estimated ingredient needs from budgeted product volumes.
Each product can however be produced with several different recipes, each containing the to-be-estimated ingredients.
I will therefore estimate the ingredient needs based on one single recipe, ie the most recent recipe (Recipe 11 for Product 1 and Recipe 21 for Product 2).
In TABLE 1, I have for each product the list of possible recipes and the ingredients amounts for each of these recipes.
In TABLE 2 I have the Budgeted Volume.
Is there a way to obtain something like the RESULTS TABLE below: INGREDIENTS per BUDGET (= [TABLE 1]AMOUNT * [TABLE 2]BUDGET VOLUME) but only according to the composition of the most recent recipe and only for the products that are listed in TABLE 2?
powax
The most simple way is you first create the "Ingredient per Budget" column in your Table1.Ingredient per Budget = [Amount] * RELATED(Table2[Budget Volumne])Then create the result table:Table = CALCULATETABLE(Table1,FILTER(Table1,Table1[Recipe]="recipe 11"||Table1[Recipe]="Recipe 21"))
Community Support Team _ Paul Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more
1 Reply
powax
The most simple way is you first create the "Ingredient per Budget" column in your Table1.Ingredient per Budget = [Amount] * RELATED(Table2[Budget Volumne])Then create the result table:Table = CALCULATETABLE(Table1,FILTER(Table1,Table1[Recipe]="recipe 11"||Table1[Recipe]="Recipe 21"))
Community Support Team _ Paul Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more