Product problem : Need help with a Measure

[SurfingData]

I have hit a wall in trying to figure out a product cost problem I am working on.

I have a product that has two kinds of inputs: Raw inputs and Manufactured inputs that we also make.

I have successfully created a parent child hierarchy table.

The last column on the right is the cost * amount * ratio. There are ratios between each level of product. So 4503 goes into 4502 at a ratio of .24. Then 4502 goes into 4501 at a ratio of 2.38. 

Currently I can get 2 levels two work correctly but any more complex product such as the one above I am not sure how to create a measure that takes the sum of 4503 ratio it, then add it to the sum of 4502 and multiply by the ratio for the 4502 level.

I am very close with my parent child table as I have all the right numbers, I just can not figure out the measure to sum ratio to the top level. Any suggestions would be appreciated.

More specifically:  How do I sum the Raw ratio column on the right based on level 2 column and then how to add the sum I can currently get but also the sum from the lower level * current level ratio? Or The same idea using the Material Cost column( which is the raw cost before applying any ratio)?
This Raw Ratio itselfs handles the two step but no further. So I think I should be using the Materials Cost before ratio and then applying ratio for each level but I am not sure

[v-yadongf-msft]

Hi @SurfingData ,

Can you tell me what's your expected output?

Thanks for your efforts & time in advance.

Best regards,

Yadong Fang

[SurfingData]

My expected output is the total cost for the product 4500.
My report at the end will be a cost of the product 4500 as well as the cost of level 1 input all the way up the hierarchy. So the report will show that level 1 input number 100331 gets used in 4500, 4501, 4502 and 4503 and has a total cost of X and total amount of Y used of 100331. 

I ideally would be able to do different level breakdowns like in this example https://www.daxpatterns.com/parent-child-hierarchies/  but instead of the stores, it would be for 4500 4501 4502 4503 etc.   Here is an excel file of the example https://docs.google.com/spreadsheets/d/1DIRmvV9pjK_xBnRUxWsXBlSIFGlqIH_5/edit?usp=sharing&ouid=10330... .   I am working on the DAX to build these hierarchies like their example and think I have this part figured out but we will see!

Please let me know if I answered your question correctly, I am unsure exaclty how to answer.

[SurfingData · Accepted solution]

So, I figured out your problem: You need to use the parent child structure that you have in place.

First, you apply the ratios for level 2 to 3 correctly, but the problem is that you stopped this process too early. You need to contiue it on. Add all the other ratio levels as new columns and that apply that ratio to the previous level . Then when you have all the levels, you need to sum only the highest level of each. If you sum all the levels you will double count.  See attached picture  to understand.

You didnt need a measure for this after all, just needed to think it through better and use the parent child structure you already built!