Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I'm struggling getting the correct totals for a time recording/ tracking report. Also, early apology for the massive post - I just wanted to include as much info as possible.
Right, I have a date dimension table and a fact table with the number of hours worked like below:
Each employee has a daily target of 7 hours. Therefore, even though this might be unecessary, i've included a column of ExpectedHours in the DateDimension table.
I need to be able to show how much time is missing per day, per employee and be able to roll that up for the week/ month/ year etc. I created a measure to sum the hours but the issue i'm getting is when an employee hasn't recorded any time at all for the day.
I partly got this working with IF(ISNULL for both adding a 0 to the HoursRecorded sum and a 7 to the ExpectedHours sum for where records do not exist. Example below (red line is where the data was missing/ green highlight is where it's fixed):
However, i'm getting an issue with the Totals for all of the sums that have the ISNULL check. It seems that, whilst the nulls are filled with the relavent 0' and 7's, these aren't included in the total calculations. Example for one employee below:
In the example above (left table), you can see that the totals for ExpectedHours and HoursMissing (the sum of ExpectedHours minus the sum of HoursRecorded) aren't including the numbers where 0 time is recorded. I'd like to get the results in the table (above right) and filter out weekends etc. myself.
I've seen similar issues with the totals not adding up but none that have the required numbers to be forced where nulls exist.
Help will be much appreciated!
Solved! Go to Solution.
I think i've got it! I stayed up till 3am to figure this out and, to my embarrassment, it looks like it should not have taken anywhere near this long to figure out.
So, i kept with a column [ExpectedHours] in the dates table as this number is a daily requirement. The formula being:
DailyExpectedHours = SUM(DimDate_Event[ExpectedHours])+IF(ISBLANK(SUM(DimDate_Event[Date])),7.0,0)
The IF(ISBLANK... sorted out the additional 7 hours per day in my above post.
The bit that really worked - I used SUMX, SUMMARIZE, CALCULATE to sum up the hours per day, per user for time entries that have been submitted (posted) like so:
HoursPosted =
SUMX(
SUMMARIZE(FctTimeEntry, FctTimeEntry[TimekeeperUserID], DimDate_Event[Date]),
CALCULATE(IF(SUM(FctTimeEntry[Hours])>[DailyExpectedHours],[DailyExpectedHours], SUM(FctTimeEntry[Hours])), FctTimeEntry[Status] = "Posted")+0
)
Then, finally, the subtraction and IF() to avoid negative numbers:
HoursMissing_Posted =
VAR _MissingHoursTotal = [DailyExpectedHours]-[HoursPosted]
return IF(_MissingHoursTotal < 0, 0, _MissingHoursTotal)
This seems to be working right now though feedback on what i've done would be greatfully appreciated as i'm very much still learning Power BI.
Thanks to @amitchandak for the "+0" tip - don't think i could have worked this out without that.
I think i've got it! I stayed up till 3am to figure this out and, to my embarrassment, it looks like it should not have taken anywhere near this long to figure out.
So, i kept with a column [ExpectedHours] in the dates table as this number is a daily requirement. The formula being:
DailyExpectedHours = SUM(DimDate_Event[ExpectedHours])+IF(ISBLANK(SUM(DimDate_Event[Date])),7.0,0)
The IF(ISBLANK... sorted out the additional 7 hours per day in my above post.
The bit that really worked - I used SUMX, SUMMARIZE, CALCULATE to sum up the hours per day, per user for time entries that have been submitted (posted) like so:
HoursPosted =
SUMX(
SUMMARIZE(FctTimeEntry, FctTimeEntry[TimekeeperUserID], DimDate_Event[Date]),
CALCULATE(IF(SUM(FctTimeEntry[Hours])>[DailyExpectedHours],[DailyExpectedHours], SUM(FctTimeEntry[Hours])), FctTimeEntry[Status] = "Posted")+0
)
Then, finally, the subtraction and IF() to avoid negative numbers:
HoursMissing_Posted =
VAR _MissingHoursTotal = [DailyExpectedHours]-[HoursPosted]
return IF(_MissingHoursTotal < 0, 0, _MissingHoursTotal)
This seems to be working right now though feedback on what i've done would be greatfully appreciated as i'm very much still learning Power BI.
Thanks to @amitchandak for the "+0" tip - don't think i could have worked this out without that.
Hi @hemalpatel ,
Glad to hear that. Please accept your reply as the solution. Your contribution is highly appreciated.
Best Regards,
Icey
@hemalpatel ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
This seems correct. Need to check with data
following measures should work
exp = sum(Table[ExpectedHours])+0
rec =Sum(Table[Hoursrecorded])+0
missing = [exp]-[rec]
Thanks, @amitchandak . I've just tried your suggestion - below are the results:
The individual records for ExpectedHours now have 14 on dates where time was recorded (though this should remain 7). Also, it seems the totals are adding up 7 hours for each day where time recordings exist i.e. 7*4 = 35.
I'm new to posting on this forum and couldn't see where to attach a .csv. Below is some tabular data for the two tables i use. I've only put in about two/three weeks worth of data (for two timekeepers) as it's enough to include full 7 hour days, partial days and zero hour days.
[TimeEntry]
TimekeeperID | EventDate | Hours |
741 | 18/10/2020 00:00 | 0.5 |
741 | 19/10/2020 00:00 | 0.2 |
741 | 19/10/2020 00:00 | 0.4 |
741 | 19/10/2020 00:00 | 0.8 |
741 | 19/10/2020 00:00 | 1.9 |
741 | 19/10/2020 00:00 | 9.2 |
367 | 20/10/2020 00:00 | 0.2 |
367 | 20/10/2020 00:00 | 0.7 |
367 | 20/10/2020 00:00 | 0.8 |
367 | 20/10/2020 00:00 | 1 |
367 | 20/10/2020 00:00 | 1.3 |
367 | 20/10/2020 00:00 | 1.5 |
367 | 20/10/2020 00:00 | 2 |
741 | 20/10/2020 00:00 | 1.9 |
741 | 20/10/2020 00:00 | 9.6 |
367 | 21/10/2020 00:00 | 0.3 |
367 | 21/10/2020 00:00 | 0.4 |
367 | 21/10/2020 00:00 | 1.3 |
367 | 21/10/2020 00:00 | 1.5 |
367 | 21/10/2020 00:00 | 2 |
741 | 21/10/2020 00:00 | 0.6 |
741 | 21/10/2020 00:00 | 8.4 |
367 | 22/10/2020 00:00 | 0.2 |
367 | 22/10/2020 00:00 | 0.4 |
367 | 22/10/2020 00:00 | 0.5 |
367 | 22/10/2020 00:00 | 1.5 |
367 | 22/10/2020 00:00 | 4.6 |
741 | 22/10/2020 00:00 | 0.5 |
741 | 22/10/2020 00:00 | 2.8 |
367 | 23/10/2020 00:00 | 0.3 |
367 | 23/10/2020 00:00 | 0.8 |
367 | 23/10/2020 00:00 | 0.9 |
367 | 23/10/2020 00:00 | 2 |
741 | 23/10/2020 00:00 | 1.4 |
367 | 26/10/2020 00:00 | 0.3 |
367 | 26/10/2020 00:00 | 0.8 |
367 | 26/10/2020 00:00 | 0.9 |
741 | 26/10/2020 00:00 | 4.9 |
367 | 27/10/2020 00:00 | 0.2 |
367 | 27/10/2020 00:00 | 0.3 |
367 | 27/10/2020 00:00 | 0.6 |
367 | 27/10/2020 00:00 | 0.7 |
367 | 27/10/2020 00:00 | 1.6 |
741 | 27/10/2020 00:00 | 4.9 |
367 | 28/10/2020 00:00 | 0.4 |
367 | 28/10/2020 00:00 | 0.5 |
741 | 28/10/2020 00:00 | 8.3 |
367 | 29/10/2020 00:00 | 0.8 |
367 | 29/10/2020 00:00 | 1.4 |
741 | 29/10/2020 00:00 | 7.8 |
367 | 30/10/2020 00:00 | 0.2 |
367 | 30/10/2020 00:00 | 0.4 |
741 | 30/10/2020 00:00 | 3.7 |
367 | 02/11/2020 00:00 | 0.3 |
367 | 02/11/2020 00:00 | 0.4 |
367 | 02/11/2020 00:00 | 0.7 |
367 | 03/11/2020 00:00 | 0.3 |
367 | 03/11/2020 00:00 | 0.4 |
367 | 03/11/2020 00:00 | 1.2 |
367 | 05/11/2020 00:00 | 0.3 |
367 | 05/11/2020 00:00 | 0.4 |
367 | 05/11/2020 00:00 | 1.7 |
367 | 06/11/2020 00:00 | 0.2 |
367 | 06/11/2020 00:00 | 0.3 |
367 | 06/11/2020 00:00 | 0.5 |
367 | 06/11/2020 00:00 | 0.7 |
367 | 06/11/2020 00:00 | 0.8 |
[DimDate]
Date | IsWeekend |
05/10/2020 00:00 | FALSE |
06/10/2020 00:00 | FALSE |
07/10/2020 00:00 | FALSE |
08/10/2020 00:00 | FALSE |
09/10/2020 00:00 | FALSE |
10/10/2020 00:00 | TRUE |
11/10/2020 00:00 | TRUE |
12/10/2020 00:00 | FALSE |
13/10/2020 00:00 | FALSE |
14/10/2020 00:00 | FALSE |
15/10/2020 00:00 | FALSE |
16/10/2020 00:00 | FALSE |
17/10/2020 00:00 | TRUE |
18/10/2020 00:00 | TRUE |
19/10/2020 00:00 | FALSE |
20/10/2020 00:00 | FALSE |
21/10/2020 00:00 | FALSE |
22/10/2020 00:00 | FALSE |
23/10/2020 00:00 | FALSE |
24/10/2020 00:00 | TRUE |
25/10/2020 00:00 | TRUE |
26/10/2020 00:00 | FALSE |
27/10/2020 00:00 | FALSE |
28/10/2020 00:00 | FALSE |
29/10/2020 00:00 | FALSE |
30/10/2020 00:00 | FALSE |
31/10/2020 00:00 | TRUE |
01/11/2020 00:00 | TRUE |
02/11/2020 00:00 | FALSE |
03/11/2020 00:00 | FALSE |
04/11/2020 00:00 | FALSE |
05/11/2020 00:00 | FALSE |
06/11/2020 00:00 | FALSE |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |