topic Re: SUMX over multiple tables in DAX Commands and Tips

SUMX over multiple tables

romeo — Tue, 22 Mar 2022 12:52:58 GMT

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 🙂

Re: SUMX over multiple tables

johnt75 — Tue, 22 Mar 2022 12:29:52 GMT

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 ) )

Re: SUMX over multiple tables

romeo — Tue, 22 Mar 2022 12:55:09 GMT

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.

Re: SUMX over multiple tables

johnt75 — Tue, 22 Mar 2022 13:45:57 GMT

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 ) )

Re: SUMX over multiple tables

romeo — Tue, 22 Mar 2022 13:59:35 GMT

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

Re: SUMX over multiple tables

Whitewater100 — Wed, 23 Mar 2022 00:55:04 GMT

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.

Re: SUMX over multiple tables

johnt75 — Wed, 23 Mar 2022 09:09:25 GMT

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.

Re: SUMX over multiple tables

Whitewater100 — Wed, 23 Mar 2022 19:16:52 GMT

Thank you for explaining. I appreciate that!