In your Excel file, in the “Attendance File” worksheet it shows 3 employees were Absent and out of which 1 employee has “Applied for Regularization” and 1 has applied for “Outdoor” request so ideally 3 employees should be present on 01-07-2020 whereas in your file under “Sheet 4” result you’ve showed only 2 employees were present. Below is the screenshot provided for the reference.
Now as per your, calculation it shows the % rate is 50% whereas in my calculation if I consider these employees as present then the absent rate for that day will fall down to 25%. Below is the screenshot provided for the reference.
Rest all the figures are good to go.
I’m also attaching the Excel File as well as the PBIX file of my working for the reference.
Hoping you find this useful and helps you in your analysis.
Please Note: I’ve used the “Measure Branching Technique” to solve your case.
Thanks & Warm Regards,
Absent Data.xlsx (15.5 KB)
Attendance Calculation.pbix (128.1 KB)