0

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.

jordan23
  • 73
  • 1
  • 12
  • Hi. You need to read a bit of data modeling. If you relate the tables you don't need to think of solving that on dax. If you relate tables, adding a column like "CALCULATE(SUM('Labor'[Amount])" in budget will detect automatically the values for each row. – ibarrau Sep 11 '19 at 17:54
  • yes i have both tables connected through the section column. But that did not work, when i enter that each row was populated with the same number; a sum of all the amount regardless of section. Unless i did something wrong. – jordan23 Sep 11 '19 at 19:04

1 Answers1

1

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.

greggyb
  • 3,728
  • 1
  • 11
  • 32
  • You are amazing, I was not aware of these funtions because I am fairly new to PowerBi. I appreciate your time and thank you for the explanation. – jordan23 Sep 11 '19 at 17:56