Calculating expression based on related tables

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.