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

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's avatar
romeo
Frequent Visitor
4 years ago

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.