Search code examples
powerbicalculated-columnssumifs

How to add numbers from different tables in PowerBi?

I am having a problem trying to find a sumif funtion for PowerBi. I have two tables a labor and a budget. My labor has the following columns:

Section Amount invoice 1.a $10 1 1.b $11 1 1.c $12 1 1.a $22 2 1.d $100 2 1.q $50 2 1.c $1 3

and my budget table looks like as follows:

Section Budget-Amount 1.a $100 1.b $200 1.c $250 1.d $150 1.q $90 . . .

What I want to do is add another column to the budget table names Budget remaining. What this would do is add all the amounts that pertains to the section on the current row from labor and subtract that from Budget amount.

I've looked up examples on how to do this but it appears that there are only examples when using the same table. But I also keep reading that the calculate function is more powerful than sumif.

I've tried to use the calculate funtion as follows: Remaining Budget = ('Budget'[Budget Amount])- CALCULATE(SUM('Labor'[Amount]),'Labor'[Section]='Budget Labor'[Section])

the error that i get is "The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression."

I'm sorry if this is a dumb question, I just cannot seem to find the right answer online.

Thank you in advance.

Solution

  • The error is spot on, but it's useless if you don't already know what it means.

    CALCULATE args 2-N can take, among other things, simple predicates, e.g. CALCULATE ( ..., 'Table'[Field] = <expression> ). This type of simple predicate can only reference one column. This is because they are rewritten behind the scenes (more on this here), and that rewriting doesn't support arbitrary expressions.

    You've tried to write: 'Labor'[Section] = 'Budget'[Section]. This is referencing two columns.

    Remaining Budget =
VAR CurrentBudgetRowSection = 'Budget'[Section]
RETURN
  'Budget'[Budget Amount]
    - CALCULATE(
        SUM( 'Labor'[Amount] ),
        'Labor'[Section] = CurrentBudgetRowSection
      )

    We can reference a variable in a simple predicate in CALCULATE, so we first capture the current 'Budget' row's section in a variable and then reference that in the CALCULATE later.