How do I deal with missing data in a data source(https://drive.google.com/file/d/1w_-i4JvdcYApxtI3QD3MUOE52NYYvcxx/view?usp=sharing ), any tips or tricks?
My analysis so far:
Reports To Code has 462 missing values.
Time Sheet Status has 90 missing values.
The missing values in the “Reports To Code” and “Time Sheet Status” columns might be of particular concern because they suggest missing administrative data that is likely expected to be present for each record.
The summary statistics for the numerical columns provide the following insights:
Actual Billable Hours For Week: Mean is around 14.23 hours with a maximum of 58.34 hours, suggesting some high variations in billable hours, which may be expected depending on workload and contracts.
Actual Hours For Week: The average is higher than billable hours at approximately 26.85 hours, with a maximum of 74.33 hours, which might include non-billable work.
Approved By: This seems to be a categorical field that might be encoded as numeric, representing different approvers or departments with IDs ranging from 4 to 172.
Billable Hours For Week: On average, around 19.15 hours, with a maximum of 93.5 hours, which is quite high and may warrant further investigation to ensure accuracy.
Daily Capacity: Most values are around 7.5 to 8 hours, aligning with typical workday expectations.
Weekly Capacity: Generally 37.5 to 40 hours, which is standard for a full-time workweek.
Weekly Leave: Averages to approximately 3.68 hours, with a maximum of 40 hours, indicating that some entries represent a full week of leave.
Columns related to specific services such as Data & Development Services - Weekly Actual Billable Hours and Professional Services - Weekly Actual Billable Hours have mean values that suggest active tracking of hours for these services, with a reasonable spread.
Utilization Rate:
Billable Utilization: The percentage of actual billable hours to total available hours.
Overall Utilization: The percentage of total actual hours (billable and non-billable) to total available hours.
Billable Hours:
Total Billable Hours: The total number of hours billed to clients.
Average Billable Hours per Employee: The average number of billable hours per employee or per role.
Capacity Utilization:
Average Daily Capacity Utilization: Average number of hours worked per day divided by the daily capacity.
Weekly Capacity Utilization: Total actual hours for the week divided by the total weekly capacity.
Revenue Efficiency:
Revenue per Billable Hour: Total revenue divided by total billable hours.
Revenue per Employee: Total revenue divided by the number of employees.
Leave and Absenteeism:
Average Weekly Leave: The average hours of leave taken per week.
Leave Rate: The percentage of hours taken as leave out of total available hours.
Project Performance:
Utilization.pbix (263.4 KB)