Determination of the absence rate

joslaz

Board Regular
Joined
May 24, 2018
Messages
76
Hello everybody,


I am currently trying to determine from the data model, how high the absence rate for the individual weeks or months.
So far, I've just added the factor to the pivot calendar, whether it's a workday.


The table with the presence or absence of the employees is structured as follows:
DateNameShiftState
01.01.2019AntonEarlyX
02.01.2019AntonEarlyX

<tbody>
</tbody>



Employees on holiday are in the state with U marked for the respective day.


I. How would you determine the absence rate using a measure?
II. How would you determine the absence rate, if there are still employees who work on the regular weekend but have Monday and Tuesday free?





I am grateful for every tip / approach!
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Hey Matt, thanks for your approach!

I have worked out an approach like that:

= (
COUNTROWS (
FILTER (
'Process Staffing Total';
'Process Staffing Total'[Status]
IN {
"U";
"P"
}
)
)
)
/ CALCULATE (
SUM ( 'Calendar'[Workdayfactor] )
)

So this functions sums all rows, that contains U or P (addional day off) and dived the sum by the workday factor sum.
This approach just shows empolees which are absence. But I want also the employees, that are present.
How can I include also the 0% absence rate for employees, that are present?
 
Upvote 0
With the following function the pivot contains also the blanks.
Code:
[COLOR=#333333][FONT=Tahoma][I]=( ([/I][/FONT][/COLOR]
[COLOR=#333333][FONT=Tahoma][I]COUNTROWS ([/I][/FONT][/COLOR]
[COLOR=#333333][FONT=Tahoma][I]FILTER ([/I][/FONT][/COLOR]
[COLOR=#333333][FONT=Tahoma][I]'Process Staffing Total';[/I][/FONT][/COLOR]
[COLOR=#333333][FONT=Tahoma][I]'Process Staffing Total'[Status][/I][/FONT][/COLOR]
[COLOR=#333333][FONT=Tahoma][I]IN {[/I][/FONT][/COLOR]
[COLOR=#333333][FONT=Tahoma][I]"U";[/I][/FONT][/COLOR]
[COLOR=#333333][FONT=Tahoma][I]"P"[/I][/FONT][/COLOR]
[COLOR=#333333][FONT=Tahoma][I]}[/I][/FONT][/COLOR]
[COLOR=#333333][FONT=Tahoma][I])[/I][/FONT][/COLOR]
[COLOR=#333333][FONT=Tahoma][I])[/I][/FONT][/COLOR]
[COLOR=#333333][FONT=Tahoma][I])[/I][/FONT][/COLOR]
[COLOR=#333333][FONT=Tahoma][I]+0)/ CALCULATE ([/I][/FONT][/COLOR]
[COLOR=#333333][FONT=Tahoma][I]SUM ( 'Calendar'[Workdayfactor] )[/I][/FONT][/COLOR]
[COLOR=#333333][FONT=Tahoma][I])[/I][/FONT][/COLOR]

If I decrease the granularity (no name) the Average just sums up. There is no avergage - just a huge number.
How can I adapt the average?
So the pivot doesnt show the absence rate per employee > the average of all employes should be shown.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top