Forum Discussion

olegkazanskyi's avatar
olegkazanskyi
Helper II
3 years ago
Solved

Getting Item Components Amount Based on Sales

 

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's avatar
    johnt75
    3 years ago

    Try

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

4 Replies

  • johnt75's avatar
    johnt75
    Super User
    3 years ago

    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's avatar
      olegkazanskyi
      Helper II
      3 years ago

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

      • johnt75's avatar
        johnt75
        Super User
        3 years ago

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