Help Calculating & Summarizing the % of hours of absence per employee [UPDATED]

[Anonymous]

Hi all,

so I have the following data (this is a simplified and ramdomized sample).

I need to calculate the % of time an employee has been absent during 2018.

The employees in my example are expected to work different amounts of hours [TARGET HOURS] over the 12 months , based on the duration of their contracts.

I need to build a pivot where I can see a summary BY DIVISION of the % of time people were absent.

YEAR 2018

DIVISION

EMPLOYEE ID

My problem is that if I use the simple "calculated field" the summary is not correct since Excel does not sum the total number of hours all employees with absences, by  division, are supposed to work.

Example : there are 2 employees in operations that were absent, therefore the % should be calculated on 2500 (1800+700) hours , the sum of the total hours for both of them.

Sample Data

Employeed ID	Division	Date Absent	Hours Absent	Target hours
10138	Operations	27/01/2018	4	1800
10138	Operations	28/01/2018	12	1800
10021	Marketing	28/01/2018	12	1000
10021	Marketing	29/01/2018	12	1000
10138	Operations	29/01/2018	2	1800
10135	Procurement	29/01/2018	12	900
10021	Marketing	30/01/2018	12	1000
10138	Operations	30/01/2018	3	1800
10135	Procurement	30/01/2018	12	900
10138	Operations	05/02/2018	12	1800
10135	Procurement	05/02/2018	12	900
10138	Operations	06/02/2018	12	1800
10135	Procurement	06/02/2018	6	900
10135	Procurement	07/02/2018	5	900
10138	Operations	07/02/2018	12	1800
10135	Procurement	08/02/2018	12	900
10138	Operations	08/02/2018	12	1800
10138	Operations	12/02/2018	12	1800
10135	Procurement	12/02/2018	8	900
10138	Operations	14/02/2018	12	1800
10135	Procurement	14/02/2018	12	900
10138	Operations	15/02/2018	12	1800
10138	Operations	16/02/2018	12	1800
10063	Sales	23/02/2018	12	1800
10138	Operations	23/02/2018	3	1800
10063	Sales	24/02/2018	12	1800
10138	Operations	24/02/2018	12	1800
10138	Operations	25/02/2018	12	1800
10063	Sales	25/02/2018	12	1800
10084	Operations	04/05/2018	12	700
10084	Operations	05/05/2018	9	700
10084	Operations	06/05/2018	12	700

[v-frfei-msft · Accepted solution]

Hi  @Anonymous ,

Here we go.

Measure = 
VAR targ =
    SUMX (
        VALUES ( 'Table'[Employeed ID] ),
        CALCULATE (
            MAX ( 'Table'[Target hours] ),
            ALLEXCEPT ( 'Table', 'Table'[Date Absent].[Year], 'Table'[Employeed ID] )
        )
    )
VAR su =
    SUM ( 'Table'[Hours Absent] )
RETURN
    DIVIDE ( su, targ )

Pbix as attached.

[Anonymous]

Dear @v-frfei-msft  Frank,

thank you so so much for your help! 😎

Unfortunately due to my lack of precision, this turned into quite and adventure for me, since I realised I posted this issue in the wrong forum, I am using PowerBI in Excel 2016, not BI Desktop!

I did manage to get it to work, changing things a bit since I am very new on all this.

I also realised it was wrong to sum together the total hours for both years (18&19) so I did 2 separate files.

I marked your post as solution , but the actual code I used in Excel DataModel is this:

Pct Time Absnt 19:=
DIVIDE(
SUM(Report_2019[Hours Absent]), 
SUMX(
VALUES(Report_2019[Employee ID]), 
CALCULATE(
MAX([Hours Target 19]), 
ALLEXCEPT(Report_2019,Report_2019[Year],Report_2019[Employee ID]))))

One last question, as I am still studying your code, what is the "ALLEXCEPT" formula doing?

[Anonymous]

@Anonymous Please try below measure.

Measure = 
VAR _countofEmp = CALCULATE(DISTINCTCOUNT('Table'[Employeed ID]),ALLEXCEPT('Table','Table'[Division],'Table'[Date Absent].[Year]))
VAR _expectedHours =  _countofEmp*1800
VAR _absentHours = CALCULATE(SUM('Table'[Hours Absent]),ALLEXCEPT('Table','Table'[Division],'Table'[Date Absent].[Year]))
RETURN DIVIDE(_absentHours,_expectedHours,0)

Please let me know if you have any question. If it helps accept as solution. 

[Anonymous]

Dear @Anonymous , once again thank you so so much for the prompt and kind reply.

I realized a few moments ago that my question was not formulated correctly and I have updated the original post.

I gave the wrong information.

The employees have  different target total working hours for the year.

Some of them worked 12 months, but some others worked less.

So I have now changed the example data in the original post and I am also copying the same table here below.

Would you please be so kind to help me once more?

Employeed ID	Division	Date Absent	Hours Absent	Target hours
10138	Operations	27/01/2018	4	1800
10138	Operations	28/01/2018	12	1800
10021	Marketing	28/01/2018	12	1000
10021	Marketing	29/01/2018	12	1000
10138	Operations	29/01/2018	2	1800
10135	Procurement	29/01/2018	12	900
10021	Marketing	30/01/2018	12	1000
10138	Operations	30/01/2018	3	1800
10135	Procurement	30/01/2018	12	900
10138	Operations	05/02/2018	12	1800
10135	Procurement	05/02/2018	12	900
10138	Operations	06/02/2018	12	1800
10135	Procurement	06/02/2018	6	900
10135	Procurement	07/02/2018	5	900
10138	Operations	07/02/2018	12	1800
10135	Procurement	08/02/2018	12	900
10138	Operations	08/02/2018	12	1800
10138	Operations	12/02/2018	12	1800
10135	Procurement	12/02/2018	8	900
10138	Operations	14/02/2018	12	1800
10135	Procurement	14/02/2018	12	900
10138	Operations	15/02/2018	12	1800
10138	Operations	16/02/2018	12	1800
10063	Sales	23/02/2018	12	1800
10138	Operations	23/02/2018	3	1800
10063	Sales	24/02/2018	12	1800
10138	Operations	24/02/2018	12	1800
10138	Operations	25/02/2018	12	1800
10063	Sales	25/02/2018	12	1800
10084	Operations	04/05/2018	12	700
10084	Operations	05/05/2018	9	700
10084	Operations	06/05/2018	12	700