Calculating expression based on related tables

[EvinMensler]

Hi.

I have a table with the forecast quantity of some products and another one with their ingredients and ratios.

Now i can get the needed ingredient quantities based on just these two tables; the problem is that some ingredients also have their components and i cant figure out how to calculate the total quantity.

pbi link 

the result:

ingredient_name = Ingredients_list[ingredient_name]

quantity_forecast = CALCULATE(SUM(Forecast[quantity]), SUMMARIZE(Ingredients, Forecast[product_name], Ingredients[ingredient_name]))

ratio_forecast = AVERAGE(Ingredients[ratio])

quantity_ingredients = [quantity_forecast] * [ratio_forecast]

ratio_components = AVERAGE(Components[ratio])

quantity_components = [quantity_ingredients] * [ratio_components]

quantity_components_i shoud get = ???

ingredient_name	quantity_forecast	ratio_forecast	quantity_ingredients	ratio_components	quantity_components	quantity_components_i shoud get
Beef				0,99		35,07
Bun	127	0,50	63,5
Cheese	506	0,19	96,1
Dough	506	0,46	232,8
Flour				0,95		221,12
Lettuce	277	0,28	76,2
Meat	127	0,21	26,7
Oil	506	0,07	35,4	0,30	11	11,64
Onion	127	0,10	12,7
Salami	506	0,11	55,7
Salt				0,01		0,35
Sauce	277	0,07	19,4
Spices				0,20		3,79
Tomato sauce	506	0,17	86,0
Tomatoes	150	0,50	75,0	0,94	71	80,86

forecast:

product_name	product_id	quantity
Salad	100	150
Pizza	101	506
Burger	102	127
Sandwich	103	12

ingredients:

product_name	product_id	ingredient_name	ingredient_id	ratio
Salad	100	Lettuce	1	0,45
Salad	100	Tomatoes	2	0,5
Salad	100	Sauce	3	0,05
Pizza	101	Dough	11	0,46
Pizza	101	Tomato sauce	12	0,17
Pizza	101	Oil	202	0,07
Pizza	101	Cheese	13	0,19
Pizza	101	Salami	14	0,11
Burger	102	Bun	16	0,5
Burger	102	Lettuce	18	0,1
Burger	102	Onion	19	0,1
Burger	102	Sauce	3	0,09
Burger	102	Meat	22	0,21

components:

ingredient_name	ingredient_id	component_name	component_id	ratio
Sauce	3	Spices	201	0,2
Sauce	3	Oil	202	0,8
Dough	11	Flour	203	0,95
Dough	11	Oil	202	0,05
Tomato sauce	12	Tomatoes	2	0,94
Tomato sauce	12	Oil	202	0,06
Meat	22	Beef	206	0,99
Meat	22	Salt	209	0,01

ingredients_list:

ingredient_name	ingredient_id
Spices	201
Oil	202
Flour	203
Tomatoes	2
Beef	206
Salt	209
Lettuce	1
Sauce	3
Dough	11
Tomato sauce	12
Cheese	13
Salami	14
Bun	16
Lettuce	18
Onion	19
Meat	22

Relations:

Ingredients_list[ingredient_id] 1:* Components[component_id]

Ingredients_list[ingredient_id] 1:* Ingredients[ingredient_id]

Ingredients[product_id] 1:* Forecast[product_id]

[burakkaragoz · Accepted solution]

Hi @EvinMensler ,

This is a classic Bill of Materials (BOM) Explosion scenario. It is tricky because you are dealing with two layers of demand simultaneously:

1. Direct Demand: When the product uses the item directly (e.g., Pizza uses Oil).

2. Indirect (Derived) Demand: When the product uses an ingredient (like Dough), and that ingredient uses the item (Oil).

The problem with your current calculation is Context. When you select "Oil" in your visual, Power BI filters everything to "Oil". It cannot "see" the demand for "Dough" or "Sauce" unless we explicitly tell DAX to look for the Parent Items of the selected component.

Here is how you can calculate the Indirect Demand and add it to your total.

The Solution Measure

We need to iterate through the Components table to find all the "Parents" of the selected ingredient, calculate their demand, and then multiply by the component ratio.

Total Quantity Required = 
VAR DirectDemand = [quantity_ingredients]

VAR IndirectDemand = 
    SUMX(
        -- Iterate through the rows in the Components table (filtered by the selected Ingredient)
        'Components',
        
        VAR ComponentRatio = 'Components'[ratio]
        VAR ParentID = 'Components'[ingredient_id] -- The ID of the Parent (e.g., Dough)
        
        -- Calculate the demand for the Parent Ingredient
        VAR ParentDemand = 
            CALCULATE(
                [quantity_ingredients],
                -- Step 1: Remove the filter from the selected Child (Oil)
                ALL('Ingredients_list'),
                -- Step 2: Apply the filter for the Parent (Dough)
                'Ingredients_list'[ingredient_id] = ParentID
            )
            
        RETURN ParentDemand * ComponentRatio
    )

RETURN DirectDemand + IndirectDemand

How it works:

1. DirectDemand: Keeps your existing logic for Level 1 usage.

2. IndirectDemand: It looks at the Components table. If "Oil" is selected, it finds rows where Oil is a component (Parents: Dough, Sauce, etc.).

3. CALCULATE: This is the magic part. It uses ALL to ignore that you selected "Oil", and then uses the ParentID to pretend we selected "Dough". It calculates how much Dough is needed using your existing measure.

4. Result: It multiplies the Dough Demand * Oil Ratio and sums it up for all parents.

Important Note: This solution assumes a 2-Level Hierarchy (Product > Ingredient > Component). If you have deeper levels (e.g., Component > Sub-Component), you would need a more complex recursive pattern using PATH functions or Python/SQL prep. But for the structure you shared, this DAX pattern is the most efficient way.

Give this a try and let me know if the numbers match your expectations!

---

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
This response was assisted by AI for translation and formatting purposes.

[EvinMensler]

You're a wizard! Thank you!

[burakkaragoz]

Haha, thank you @EvinMensler ! I appreciate the kind words.

DAX Context Transition can definitely feel like dark magic sometimes. But once you master how CALCULATE and ALL work together, you are the one controlling the wand.

Since the formula is working for you, could you please mark the previous reply as the Accepted Solution? It helps others find this BOM pattern quickly when they get stuck on the same issue.

Happy reporting!

[EvinMensler]

On a followup check it seems the grand total is not the sum of all ingredients. How can i change it?