topic Re: Getting Item Components Amount Based on Sales in DAX Commands and Tips

Getting Item Components Amount Based on Sales

olegkazanskyi — Mon, 17 Jul 2023 21:34:29 GMT

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.

Re: Getting Item Components Amount Based on Sales

johnt75 — Tue, 18 Jul 2023 09:30:41 GMT

Try

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

Re: Getting Item Components Amount Based on Sales

olegkazanskyi — Tue, 18 Jul 2023 12:41:37 GMT

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

Re: Getting Item Components Amount Based on Sales

johnt75 — Tue, 18 Jul 2023 13:17:34 GMT

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

Re: Getting Item Components Amount Based on Sales

olegkazanskyi — Tue, 18 Jul 2023 13:26:31 GMT

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