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

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

  • Whitewater100's avatar
    Whitewater100
    Solution 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
      Super 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
        Solution Sage
        4 years ago

        Thank you for explaining. I appreciate that!