Divide a subcategory by the qty of a higher category

[Smeek]

Dear Power BI Pros,

I have a problem that I cannot find a measure that calculates the material costs per quantity of finished product.

I tried:

Costs Material p Finished Good = DIVIDE(SUM(Facts[Costs Material]),SUM(Facts[Qty Finished Product]))

but I am missing the link between materials used and the quantity of the finished product (the production order number) in the formula.

I have a fact table with Production order numbers, finished products numbers, their quantity and costs, as well as the material used to produce these finished products, also their quantities (so the quantities of the materials used) and the costs.

A production order always has several lines. One for the product that was produced (the finished product and the quantity) and then the materials that were used.

I know would like to divide the material costs through the quantity of the finished product to get the costs per finished product.

Here is the visual that I would like to finish:

Here is the star scheme and the fact table:

Can someone help?

Thanks,

Smeek

[stevedep]

With some sample data, sure.

https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-... 

[Smeek]

Here goes, I hope that helps:

Prod. Order No	Finished Product	Costs Finished Product	Qty Finished Product	Material No	Qty Mterial	Costs Material
PON-400				MN-384	1	0
PON-400				MN-383	2	0
PON-400				MN-386	4	21
PON-400				MN-355	1	1.709
PON-400				MN-1093	1	38
PON-400	FP-3	1.816	5
PON-401				MN-384	1	0
PON-401				MN-383	5	0
PON-401				MN-386	1	21
PON-401				MN-355	1	1.709
PON-401				MN-1093	1	38
PON-401	FP-3	1.816	2
PON-410				MN-384	8	0
PON-410				MN-383	9	0
PON-410				MN-386	9	21
PON-410				MN-355	9	1.709
PON-410				MN-1093	9	38
PON-410	FP-3	1.820	7

[Smeek]

I managed to change the calculation to:

Costs Material p Finished Good = DIVIDE(SUM(Facts[Costs Material]),CALCULATE(Sum(Facts[Qty Finished Product]),ALLEXCEPT(Facts,Facts[Production Order No])))

This kind of works, as long as I take the Production Order Number from the Fact table and not from the Dimension table. 

How can I connect the formula to the Dimension table? Or is there another way to calculate teh material costs on basis of the Finished Product Quantity?

Thanks for any help

[Anonymous]

OK. I've got a pbix file with the solution. Please bear with me as I'm at work right now and can't put the file on a shared drive... I'll do it in a bit.

[daxer-almighty · Accepted solution]

Here's the file with the solution that I was talking about above...

https://1drv.ms/u/s!ApyQEauTSLtOgY8soLaHD-juLe_hYw?e=iGqXso

[Smeek]

Thanks a lot for your help and the file. 

I guess the main message is that I need to split the fact table into two, one for the finished products and one for the materials.

Or get my fact table cleaned up so the finished product ID is entered on the same line as the material numbers. 

Since it was quicker so slit the fact table, I tried it on the original data and it works 🙂

Thanks for your help!

[Anonymous]

Create 3 dimensions:

> Order, Product, Material

Create 2 fact tables:

> Order|Product|Prod Qty|Prod Cost

> Order|Product|Material|Material Qty|Material Cost

Join the dims to the facts (1:* with the standard one-way filtering). This way you will have isolated all the dims you need and the fact tables capture 2 processes: the manufacturing of products and the bill of materials needed to manufacture the products. This model will be simpler to calculate what you want. The fact tables will all be hidden since you should NEVER slice by columns in a fact table. Only via dimensions.