topic Re: DAX to Calculate Cost of Ingredients in DAX Commands and Tips

DAX to Calculate Cost of Ingredients

JNelson — Mon, 23 May 2022 04:31:50 GMT

Hello,

I've read through similar questions to mine but I can't quite get them to work for my data.

I have three tables:

1. Sales (includes SKU and Qty)

2. Components (Includes SKU and the 15 different components/ingredients that make up the product) there are 65 different products.

3. Cost (Has the components/ingredients and cost of each).

I'm having difficulty calculating the cost per sale. I've done a workaround but there must be a simpler way. I've gone the (super) long way around and done:

CostPerSale=SUMX(NATURALINNERJOIN(Sales,Components),[Qty]*Components[Ingredient1]*SUM(Cost[Ingredient1])) +

SUMX(NATURALINNERJOIN(Sales,Components),[Qty]*Components[Ingredient2]*SUM(Cost[Ingredient2])) +

SUMX(NATURALINNERJOIN(Sales,Components),[Qty]*Components[Ingredient3]*SUM(Cost[Ingredient3]))......... until I get to ingredient 15!

Please help 🙂

Thank you!

Re: DAX to Calculate Cost of Ingredients

DimaMD — Mon, 23 May 2022 06:43:21 GMT

Hi JNelson Please provide a sample of your data in a pbix file or spreadsheet, thank you

Re: DAX to Calculate Cost of Ingredients

JNelson — Mon, 23 May 2022 22:44:18 GMT

Hi DimaMD ,

Thank you for your reply. The information is sensitive. Will the below tables be enough to get assistance?

SALES TABLE

Customer	SKU	Qty	Total Sell Price
Bob Smith	112668	4	$495.00
Bob Smith	112670	2	$217.00
Jane Brown	112676	56	$5,098.00
Max Tom	134786	3	$475.00

COMPONENTS

SKU	IngA	IngB	IngC	IngD	IngE	IngF	IngG	IngH
112668	0.25	0.10	0.03	0.05	0.07	0.30	0.02	0.18
112670	0.05	0.15	0.70	0.00	0.00	0.02	0.06	0.02
112676	0.40	0.00	0.00	0.05	0.50	0.05	0.00	0.00
134786	0.04	0.30	0.00	0.08	0.29	0.20	0.09	0.00

COST

Component	Ingredient Cost
Ing A	$1250.00
IngB	$42.08
IngC	$42.08
IngD	$45.08
IngE	$45.08
IngF	$431.00
IngG	$27.78
IngH	$25.50

Re: DAX to Calculate Cost of Ingredients

Anonymous — Thu, 26 May 2022 06:42:49 GMT

Hi JNelson ,

What is your desired output? It seems you need to calculate the result by "the 65 different products." If so , maybe your formula is the only one. If I have misunderstood your meaning, please provide your desired output with more details.

How to Get Your Question Answered Quickly

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: DAX to Calculate Cost of Ingredients

JNelson — Thu, 26 May 2022 22:45:18 GMT

Hi Anonymous

Essentially I need to look up the SKU purchased and then carry out a SUMPRODUCT on the components of that SKU against the cost of the components. My problem is that they are all in different tables.

For example the cost of Max Tom's purchase (SKU 134786) would be:

(0.04 * 1250) + (0.30 * 42.08) + (0.00 * 42.08) + (0.08 * 45.08) + (0.29 * 45.08) + (0.20 * 431.00) + (0.09 * 27.78) + (0.00 * 25.50) = $168.00

Then multiply it the QTY = $168 * 3 = $504.01

The comment around the 65 products is that there are 65 prodcts that the customer can choose from.

Thanks

Re: DAX to Calculate Cost of Ingredients

Whitewater100 — Fri, 27 May 2022 00:00:53 GMT

Hi:

Perhaps you can have a several tables:

Dates - Date, Month, Year, Week No., YR-MO

Share_of_Cost = Item, Component, Share (This table woul be longer as each items compents and share is set to 100%

Material_Cost = Component, Price

Once these tables are in place a more streamlined measure can work. If you have data for example, that would be great.

Thanks..

Re: DAX to Calculate Cost of Ingredients

milanpasschier2 — Mon, 06 Jun 2022 12:04:41 GMT

Hey, you have to reformat your COMPONENTS table structure to:

SKU	Component	Share
134786	IngA	0.04
134786	IngB	0.3
134786	IngC	0
134786	IngD	0.08
134786	IngE	0.29
134786	IngF	0.2
134786	IngG	0.09
134786	IngH	0

The measures would be

Cost of Ingredients = VAR _Share = MAX(Misc[Share]) VAR _Cost = MAX(Cost[Ingredient Cost]) VAR _Qty = MAX(Sales[Qty]) return _Share * _Cost * _Qty

Total Cost of Ingredients = SUMX(VALUES(Misc), [Cost of Ingredients])

Then you put Sales[Customer] and [Total Cost of Ingredients] in a Table

You can download the PBIX file here: https://milanpasschier2.s3.eu-central-1.amazonaws.com/Com+1.pbix

Please mark my answer as the solution if it fits your needs.

All the best,

Milan