Forum Discussion

romeo's avatar
romeo
Frequent Visitor
4 years ago
Solved

SUMX over multiple tables

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 ...
multiple tables
sumx
  • johnt75's avatar
    johnt75
    4 years ago

    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
)
)
johnt75's avatar
johnt75
Icon for Super User rankSuper User
4 years ago

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's avatar
Whitewater100
Icon for Solution Sage rankSolution Sage
4 years ago

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's avatar
    johnt75
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      Whitewater100
      Icon for Solution Sage rankSolution Sage
      4 years ago

      Thank you for explaining. I appreciate that!