Forum Discussion
SUMIFS in Power Bi
Hi, i'm pretty new to power Bi and am struggling to get my head around tasks that are simple in excel, i have a list of orders each with multiple time entries by different people over various months.
Normally in excel, i would use SUMIFS to sum the time column, if the order column matches order on the current row and the Month Column matches Month on the current row, which would give me the total for the order in that period. I would then divide the hours logged on that line by the total hours logged in the month to get the %age by that reviewer in the month. is this doable in power bi? i have two tables on summarises time by order, reviewer, year & month and a second table that summarises time by order, year then month. but i'm struggling to merge them with multiple parameters
example of what i'm looking to create:
| Order | Reviewer | Month | Hours | Total Hours in Mth | Percentage logged |
| 1 | John | Jan | 1 | 5 | 20.00% |
| 1 | Nancy | Jan | 4 | 5 | 80.00% |
| 1 | Gregg | Feb | 5 | 8 | 62.50% |
| 1 | Bill | Feb | 1 | 8 | 12.50% |
| 1 | Ann | Feb | 2 | 8 | 25.00% |
5 Replies
Sure, but you don't need SUMIFS(). Power BI automatically filters for you based on the table filter connections and the visuals.
See the attached file. It returns a percent of total to help out.
The measure to get percent of total is:
Percent of Total = VAR CurrentHours = [Total Hours] VAR TotalHours = SUMX( ALL('Table'), 'Table'[Hours] ) VAR Result = DIVIDE( CurrentHours, TotalHours ) RETURN ResultThe ALL('Table') tells DAX to ignore the filters on the row, so don't tell me Ann's total, tell me the total of everyone.
If you need further help, please describe the issue in bit more detail, and what your source data looks like with samples.
How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI ForumHi 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 ZhiIf 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
