Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hello,
I am trying to calculate total hours possible (expected work hours; 36.9 hours per week per person). The trick is this: I need to include only active employees (the first filter) and I need to ignore weeks in between where they are on extended leave (the second filter). This works just fine when viewing hours possible per person, but it does not work when viewing the top level Team (when viewing in a Matrix where you can expand Team --> Employee). Any suggestions please? I tried to use a summary table that calculates hours possible per week per person, but it was not working out well either. I was thinking of using COUNTX or SUMX somehow.
You can see here the team total should be around 200 less than 1,328. Since there is no filter on the employee name, the extended leavers date range filter isn't calculating per employee. I want to calculate the hours per person first with the filters applied, and then add that up.
Solved! Go to Solution.
Thanks for the replies. When viewing the total per person, the filters are working correctly. User A should be 36.9 every week, while User B should be 0 for every week in December. I want the grant total to calculate the hours per user and then add the hours up. The row totals should be 36.9 for those weeks, but instead they are 0 or 73.8. The logic I am trying to do is something like a SUMX, where the hours is calculated per row first and then added together.
I think I have resolved the issue. I used a calculated table to first calculate the hours possible per user, per week, that met the 2 filter conditions I needed. That game me _Summary table with WeekStarting, user_email, and possible. I then used a SUMX measure to add the total hours possible. Woohoo! Hopefully this helps someone out one day. The data is setup so that I have a calendar table and timecard data, where not every person would have timecard hours submitted every single day. I then needed to calculate how many hours we expected that person/team to have submitted hours (hours possible). I also couldn't rely solely on the calculated summary table, because I could not build table relationships with the other tables due to circular dependencies.
Thanks for the replies. When viewing the total per person, the filters are working correctly. User A should be 36.9 every week, while User B should be 0 for every week in December. I want the grant total to calculate the hours per user and then add the hours up. The row totals should be 36.9 for those weeks, but instead they are 0 or 73.8. The logic I am trying to do is something like a SUMX, where the hours is calculated per row first and then added together.
I think I have resolved the issue. I used a calculated table to first calculate the hours possible per user, per week, that met the 2 filter conditions I needed. That game me _Summary table with WeekStarting, user_email, and possible. I then used a SUMX measure to add the total hours possible. Woohoo! Hopefully this helps someone out one day. The data is setup so that I have a calendar table and timecard data, where not every person would have timecard hours submitted every single day. I then needed to calculate how many hours we expected that person/team to have submitted hours (hours possible). I also couldn't rely solely on the calculated summary table, because I could not build table relationships with the other tables due to circular dependencies.
Hi @PossibleGuru123 ,
According to your statement, I think your requirement is to filter the employees who are still active.
However I think your calculation is based on your data model. I am confused about how to get result 200 based on your screenshot.
You can check amitchandak's answer, if this couldn't resolve your issue, please share a sample file with us and show us a screenshot with the result you want.
This will make us easier to find the solution.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@PossibleGuru123 , use a disconnected date table and use a measure of active/current employees as provided in the file
Current Employees = CALCULATE(DISTINCTCOUNT(Employee[Employee Id]),FILTER(Employee,Employee[Start Date]<=max('Date'[Date]) && (ISBLANK(Employee[End Date]) || Employee[End Date]>max('Date'[Date]))))
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
142 | |
75 | |
64 | |
52 | |
47 |
User | Count |
---|---|
219 | |
89 | |
73 | |
66 | |
60 |