Get absenteeism percent KPI

[Anonymous]

I have one payroll excel export that shows if an employee worked on a particular day.

I am trying to have 3 KPI showing absenteeism for last week, last month, and last quarter.

If a particular employee worked more than 40 hours for a week then it is considered full attendance (100%) for that week. 

I created a measure that partially works because it doesnt looks at an individual week. I also cant group them and show it on a card.

Absenteeism =

1-DIVIDE(MIN(SUM(Payroll[Total Hrs]),40*DISTINCTCOUNT(Payroll[Weekend])),40*DISTINCTCOUNT(Payroll[Weekend]))

Data link

https://drive.google.com/file/d/1r4yMtAMG425b8_lOLSkOeYiF0zE2bUgo/view?usp=sharing

[v-jingzhang · Accepted solution]

Hi @Anonymous 

Based on your discussion with @amitchandak  above, I would suggest modifying your measures like below. It will calculate the absenteeism based on hours.

Absenteeism = 
VAR absentWeeksTable =
    FILTER (
        SUMMARIZE (
            Payroll,
            Payroll[Emp No],
            Payroll[Weekend],
            "_1", SUM ( Payroll[Total Hrs] )
        ),
        [_1] < 40
    )
VAR absentWeeks = COUNTX ( absentWeeksTable, [Weekend] )
VAR allWeeks = DISTINCTCOUNT ( Payroll[Weekend] )
VAR hrs = SUMX ( absentWeeksTable, [_1] ) + ( allWeeks - absentWeeks ) * 40
RETURN
    1 - DIVIDE ( MIN ( hrs, 40 * allWeeks ), 40 * allWeeks )

 

Kindly let me know if this helps.
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.

[amitchandak]

@Anonymous , Yet to check your file

I taken a bit different approch, I am counting employee and week.

Measure = 1- divide( Countx(Filter(Summarize(payroll, payroll[employee], Payroll[Weekend],"_1", SUM(Payroll[Total Hrs])),[_1] >=40), [Employee]) ,
Countx(Summarize(payroll, payroll[employee], Payroll[Weekend]),[Employee]))

Here each employee will counted once per week. Please change column name as per need

[Anonymous]

This is great! 

The only problem is if an employee works for 33.50 hours, it counts as if the employee is absent for the entire week.

Below is example for employee #26, for the past 8 weeks, they only missed 14.5 hours/320 = 0.0453 and not 25% which is missing 2 weeks out of 8

[amitchandak]

@Anonymous , Try using hours in place of count 🤔

Measure = 1- divide( Countx(Filter(Summarize(payroll, payroll[employee], Payroll[Weekend],"_1", SUM(Payroll[Total Hrs])),[_1] >=40), [_1]) ,
Countx(Summarize(payroll, payroll[employee], Payroll[Weekend],"_1", SUM(Payroll[Total Hrs])),[_1]))