Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi
I'm trying to show the average hours per week per employee. The table contains Employee Name, hours, Project Name and week_number. One employee can have multiple projects in one week.
I tried the following:
Solved! Go to Solution.
Hi,
Drag Employee Name and Start of week to the Table visual and write this measure
=AVERAGE(Tracker[Hours])
Hope this helps.
Hi @ezequiel ,
Can you share some more details on the information and data, share an example of the file and expected result?
I made a simple PBIX file and I'm getting changes on a card based on a slicer of the week but this can be different from your results.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490 (courtesy of @Greg_Deckler).
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix
As example an employee has this data
EmployeeName | Hours | Start of Week | WeekNum |
John | 7 | 7/29/2019 | 1 |
John | 7 | 7/29/2019 | 1 |
John | 7 | 8/5/2019 | 2 |
John | 7 | 8/5/2019 | 2 |
John | 21 | 8/5/2019 | 2 |
John | 2 | 8/12/2019 | 3 |
John | 5 | 8/12/2019 | 3 |
John | 4 | 8/12/2019 | 3 |
John | 2 | 8/12/2019 | 3 |
John | 8 | 8/12/2019 | 3 |
John | 9 | 8/12/2019 | 3 |
John | 2 | 8/12/2019 | 3 |
John | 8 | 8/12/2019 | 3 |
Using the formula Avg_hours = CALCULATE(AVERAGE(Tracker[Hours]), ALLEXCEPT(Tracker, Tracker[EmployeeName], Tracker[WeekNum]))
Hi @ezequiel
Did you ever get an answer to this question? I want to do the same thing but I want to work out a 17 week rolling avg per employee...
Hi @Anonymous ,
The answer is marked in the post, however this is not a rolling average is just a simple average for the week selection.
Can you share you data and expected result.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThanks @MFelix
I have a data table extract (sample data) and I summarized in a calculated table the total overtime per employee per week (because there are other filters as well). I need the 17week rolling average weekly OT hours and then I need to count the number of instances where the Avg 17 rolling week OT hours was > 5 hrs.
I can calculate the rolling average but Im struggling with doing it per employee and then the count…
Data table extract | |||||
Personnel Number | Start date | Attendance hrs | Week | Threshold Cat | WSR Shift |
A | 1/10/2018 | 3 | 4 | Below | N |
A | 2/10/2018 | 6 | 4 | Below | N |
A | 3/10/2018 | 0 | 4 | Below | N |
A | 4/10/2018 | 0 | 5 | Below | N |
A | 5/10/2018 | 5 | 5 | Below | N |
B | 1/10/2018 | 10 | 4 | Below | N |
B | 2/10/2018 | 6 | 4 | Below | N |
B | 3/10/2018 | 3 | 4 | Below | N |
B | 4/10/2018 | 1 | 5 | Below | N |
B | 5/10/2018 | 2 | 5 | Below | N |
Calculated table | |||
Employee Number | Start date | Total 5 HR | Week |
A | 1/10/2018 | 9 | 4 |
A | 4/10/2018 | 5 | 5 |
B | 1/10/2018 | 19 | 4 |
B | 4/10/2018 | 3 | 5 |
Calculated table formula
WeeklyOTTbl =
SUMMARIZE (
'OT Combined',
OT Combined'[Personnel Number],
'OT Combined'[Week],
"Total 5 HR", CALCULATE(SUM ( 'OT Combined'[Attendance hours] ),'OT Combined'[Threshold Cat] = "BELOW",'OT Combined'[WSR Shift] = "S")
Hi @Anonymous ,
you don't need to do a calculated table you just need to place the values on your visualizations and make the filtering correctly.
However if you need to make a calculated table the formula would be:
WeeklyOTTbl =
SUMMARIZE (
FILTER (
'OT Combined';
'OT Combined'[Threshold Cat] = "Below"
&& 'OT Combined'[WSR Shift] = "N"
);
'OT Combined'[Personnel Number];
'OT Combined'[Week];
"Start Date"; MIN ( 'OT Combined'[Start date] );
"Total 5 Hours"; SUM ( 'OT Combined'[Attendance hrs] )
)
Be aware that I'm filtring the Shift with N because is the value you have on your data you should adjust it to your desired data.
Check PBIX file attach with the calculated table and a table visualization without any calculated values.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi,
Drag Employee Name and Start of week to the Table visual and write this measure
=AVERAGE(Tracker[Hours])
Hope this helps.
User | Count |
---|---|
128 | |
108 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |