SUMIFS in BI across multiple tables

[g1davies]

HI,

I am trying to recreate a SUMIFS statement in Power Bi in excel it looks like this:

=SUMIFS(Attendance!B:B,Attendance!A:A,Contact!A:A,Attendance!C:C,1)

How would i recreate this in Power BI??

Thanks

Gavin

[v-lili6-msft]

hi, @g1davies

      Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

Best Regards,

Lin

[Greg_Deckler]

Use CALCULATE to replace SUMIF functions. CALCULATE(SUM([Column]),FILTER(<filter clause>))

[g1davies]

Thank you for helping:

Week 1 = CALCULATE(SUM('Table_local__MIDX_RDL_Q1 (3)'[Week Number]),'Table_local__MIDX_RDL_Q1 (3)'[Week Number]=1,'Table_local__MIDX_RDL_Q1 (3)'[Attendance Detail Contact ID] = 'Non Attendance'[Contacts Detail Contact ID])

but i get this error:

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.

Can you help?

[v-lili6-msft]

hi, @g1davies

     I have tested on my side by your formula, and reproduce the issue.

There are something wrong in your formula:

First condition should  use EARLIER Function like this

 FILTER('Table_local__MIDX_RDL_Q1 (3)',EARLIER('Table_local__MIDX_RDL_Q1 (3)'[Week Number])=1)

Second condition 

what is the 'Non Attendance'[Contacts Detail Contact ID]? and what is the relationship between these two tables?

We need more details for us.

Please share your sample pbix  or some data sample and expected output for us. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.

Best Regards,

Lin

[g1davies]

HI,

Thank you for your help with this.

The file can be downloaded at https://www.dropbox.com/s/o3eq45o01j534tr/Report%20for%20Help.pbix?dl=0

Non attendance is the table I am trying to create, Hopefully the attached makes sense. Want i am trying to do is to see each week who has and has not attended, to do this i want to have a table with a column for contact Ids and the columns for each week so i can see over say 12 weeks the amount of people have attendend and the amount of people who have not attended. 

Contact ID shows all people

Attendance shows everytime someone has attended.

Thanks again

Gavin 

[v-lili6-msft · Accepted solution]

hi, @g1davies

    You just have to improve your formula as below:

Week 1 = CALCULATE(SUM('Table_local__MIDX_RDL_Q1'[Week Number]),FILTER(Table_local__MIDX_RDL_Q1,'Table_local__MIDX_RDL_Q1'[Week Number]=1&&'Table_local__MIDX_RDL_Q1'[Attendance Detail Contact ID] ='Non Attendance'[Contacts Detail Contact ID]))

Result:

Best Regards,

Lin

[Cd44hg]

What if instead of those filters equalling "1", you want it evaluated against a value from another table?