Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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
Use CALCULATE to replace SUMIF functions. CALCULATE(SUM([Column]),FILTER(<filter clause>))
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?
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
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
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
What if instead of those filters equalling "1", you want it evaluated against a value from another table?
User | Count |
---|---|
93 | |
87 | |
77 | |
72 | |
66 |
User | Count |
---|---|
116 | |
107 | |
88 | |
65 | |
63 |