Forum Discussion
SUMIFS in Power Bi
Hi JDSang ,
You said that you want to merge two tables and get below result, so if possible could you please inform me more detailed information(such as your expected output and your sample data (by OneDrive for Business))? Then I will help you more correctly.
Please do mask sensitive data before uploading.
Thanks for your understanding and support.
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
i have the below as my "core data" (Table1)
Order
Year
Month
Name
Time
3000
2020
1
John
0.1
3000
2020
2
Nancy
6.5
3000
2020
3
Bill
0.15
3000
2020
3
Dave
0.6
3000
2020
3
John
0.2
3000
2020
3
Dean
4
3000
2020
3
Nancy
16.5
3000
2020
4
Bill
0.3
3000
2020
4
Dave
0.65
3000
2020
4
John
0.1
3000
2020
4
Dean
0.85
3000
2020
4
Nancy
2
I also have the time logged by order (excluding Reviewer), this is Table 2
Order
Year
Month
Total for Month
3000
2020
1
0.1
3000
2020
2
6.5
3000
2020
3
21.45
3000
2020
4
3.9
Nancy logged 2 hours in April out of 3.90 logged in total for April, how do I work out the percentage of her 2 hours for this particular order? note this a small sample, so the check on the reviewer, month and order needs to be change on every row, like a SUMIFS would in Excel:
Reviewer Oder Date Hours Order Hrs/Mth Order Hours by Month John 1 01-Jan 1 5 =SUMIFS(Hours,Reviewer,Current Row Reviwer,Month,current row month) Nancy 1 04-Jan 4 5 =SUMIFS(Sum Range,Criteria Range, Criteria, Criteria Range 2, Criteria 2) John 2 20-Jan 2 2 John 2 04-Feb 4 9 Nancy 2 06-Feb 5 9 John 1 07-Mar 3 3 John 3 15-Mar 2 4 Nancy 3 16-Mar 2 4 hope this explains a bit better