I’m working on a report for our manufacturing division to show independent and dependent usage for items. We frequently bundle items for marketing purposes, but manufacturing is not interested in sales of the Parent bundle. They want to know the usage for the components within the Parent. Thus, we work with independent and dependent usage.
Independent usage would be sales of the individual items by themselves. Dependent usage requires multiplying the quantity sold of each Parent times the quantity of the components in each bill of materials. I’ve used sample data from a basic model to demonstrate the challenge.
If an item is considered a Parent item, it should not be included in the report. I’m struggling with the best approach to calculate dependent usage while keeping the transaction dates from the Sales table. Also, if a bill of material changes, history from the previous bill of material needs to stay intact while new transactions should calculate against the current bill of materials.
Basic Model.pbix (916.4 KB)
Page 2 of the attached Basic Model outlines the details.