Determination of the absence rate

[joslaz]

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:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Name[/TD]
[TD]Shift[/TD]
[TD]State[/TD]
[/TR]
[TR]
[TD]01.01.2019[/TD]
[TD]Anton[/TD]
[TD]Early[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]02.01.2019[/TD]
[TD]Anton[/TD]
[TD]Early[/TD]
[TD]X[/TD]
[/TR]
</tbody>[/TABLE]



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!

 

[Matt Allington]

I wrote an article a few years ago that may help you. https://powerpivotpro.com/2016/12/how-many-working-days-has-an-employee-been-off-work/

 

[joslaz]

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?

 

[joslaz]

With the following function the pivot contains also the blanks.

[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.