March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
115 | |
76 | |
57 | |
52 | |
44 |
User | Count |
---|---|
164 | |
116 | |
63 | |
57 | |
50 |