Forum Discussion
Product problem : Need help with a Measure
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!
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=103303846805382645287&rtpof=true&sd=true . 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.
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!