SUMX over multiple tables

[romeo]

Hello,
I am relatively new to Power BI and don't know how to solve the following. Maybe someone knows the answer and could help, that would be great 🙂

I have 3 tables (see picture) (meanwhile I uploaded the file here: https://drive.google.com/drive/folders/181jeOfllqmWO1VIIQ8lMpJ0iezT9AgaQ?usp=sharing)

The first table lists sales articles with quantities in kg per month.
Each of these articles is produced out of different material components. The respective material and it's share is listed in a second table.
Now each of these raw materials has different purchase prices per months, this is listed in a third table.

I would now like to show the sum of material costs per sales article per month (orange column). I don't want to add a new calculated column tho, I would like to have this calculation completely inside a measure.
Unfortunately I was not able to create a working DAX formula. 
The formula should lookup the respective material components and it's share and multiply it with the respective purchase price with consideration of the month.
Also it would be nice if this still works in combination with a time slicer.

Thank youuuuu!
Cheers 🙂

[johnt75 · Accepted solution]

missing a comma

Material Costs = SUMX( Sales,
var currentArticle = Sales[Sales article]
var currentMonth = Sales[Month]
var currentQuantity = Sales[Quantity in KG]
return SUMX( FILTER( 'Material Share', 'Material Share'[Sales article] = currentArticle),
   var currentMaterial = 'Material Share'[Material component]
   var currentShare = 'Material Share'[Share]
   var materialCost = LOOKUPVALUE( 'Material purchase prices'[Purchase Price], 
      'Material purchase prices'[Material component], currentMaterial,
      'Material purchase prices'[Month], currentMonth
)
   return currentQuantity * currentShare * materialCost
)
)

[Whitewater100]

Hi johnt75:

That was an excellent solution! If you are not too busy can you explain how the two SUMX's interact with eachother admist the variables? Where I'm a bit confused is that the measure starts of with SUMX (which appears open ended followed by a new SUMX the reurn. Thank you.

[johnt75]

The outer SUMX iterates over all the sales, but for every sale you need to iterate over all the relevant materials hence the inner SUMX. Using more common programming languages as an analagy, think of it as nested for ... each loops.

[Whitewater100]

Thank you for explaining. I appreciate that!

[romeo]

Works like a charm! Thank you :)))) That was super fast!

[johnt75]

Try the below

Material Costs = SUMX( Sales,
var currentArticle = Sales[Sales article]
var currentMonth = Sales[Month]
var currentQuantity = Sales[Quantity]
return SUMX( FILTER( 'Material Share', 'Material Share'[Sales article] = currentArticle)
   var currentMaterial = 'Material Share'[Material]
   var currentShare = 'Material Share'[Share]
   var materialCost = LOOKUPVALUE( 'Material prices'[Purchase price], 
      'Material prices'[Material], currentMaterial,
      'Material prices'[Month], currentMonth
)
   return currentQuantity * currentShare * materialCost
)
)

[romeo]

Thank you johnt75 for your quick response! Unfortunately it did not work. In the meantime I uploaded the file, maybe this helps to solve it.