Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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.
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.
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 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.
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 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
77 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |