Complex calculation between two fact tables

[I_LOVE_POWER_BI]

Dear Communnity,

I have a rather complex problem I am struggeling with for a while. Would really appriciate if you could take the time to understand the problem first. Looking forward to your support!

I created a fake example to explain the problem: Having a table which shows the Recipes of different pies for different stores:

The "Mixed Fruit Pie" is using an ingredient called "Pie Mixture 1", which consits of different pies:

The "Pie Mixture Table" splits the ratio of "Pie Mixture 1" between 3 different ingredients (here the ingredients are the pies from the former table itself. And in this case even the "Mixed Fruit Pie" itself is an ingredient (like a workoff which is produced during the production process and will be reused again later for the next pies).

So my goal is to define the price for "Pie Mixture 1" and afterwards add it up to get the total price for the "Mixed Fruit Pie". Here the steps in excel I need to do in order to get there:

I can't add a calc. column in the "Pie Mixture Table", because each store is using different %-splits for the ingrediens.

My final table shall look like this:

I struggle here, because I choose the Pie and ingredients here, but through the context filter here, it only adds the mixture amount of "Mixed Fruit Pie" itself and ignores the two others...maybe also because of my relationship (?), which looks like this:

Hope this makes clear what my problem is...thanks a lot for your kind support!

[V-lianl-msft · Accepted solution]

Hi ,

I created a sample pbix for your reference:

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.