Filtering backwards through many-to-many relationship

[sedjackob]

I have been really struggling with this for the past week. Let me explain what is it that I'm trying to do and maybe you can help:

• A forecast is made for 1 product only. It has a date and volume to be ordered
• A product can have many forecasts
• Each product is made out of many intermediates (ingredients) using percentages totalling to 100%:
  • Example: product A = 15% intermediate 1 + 45% intermediate 2 + 40% intermediate 3

How can I create a table or chart showing the following:
monthly volumes for each intermediate (intermediate volume = product volume x intermediate percent for that product)

Below is a snapshot of my current Power BI schema:

 

[sedjackob]

 

[sedjackob]

 

[peter789]

Does this link help?
https://powerpivotpro.com/2012/11/a-mystifying-and-awesome-solution-for-many-2-many/