Getting Item Components Amount Based on Sales

[olegkazanskyi]

I have a simple data model of pizza sales with three tables:

1. Fact_Sales

2. DIM_Product

3. DIM_Ingridients

Every Pizza has a specific amount of ingredients.

I would like to know how many ingredients I use per month based on sales of pizzas.

Sorry, I can't get how to build this calculation.

[johnt75 · Accepted solution]

Try

Ingredient Usage =
SUMX (
    'DIM_Ingredients',
    VAR Sales =
        SUMX ( RELATEDTABLE ( 'FACT_Sales' ), 'FACT_Sales'[quantity] )
    VAR Result = Sales * 'DIM_Ingredients'[Usage, KG]
    RETURN
        Result
)

[olegkazanskyi]

Unfortunately, I got blank values as a result.
I'm sharing a dataset just for any case.
Link

[johnt75]

The relationship between sales and product is invalid - the keys in the product table include the size whereas the sales table doesn't.

[olegkazanskyi]

Thank you! You're a life saviour.
A silly mistake