Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

Getting the Weekly Average of Employee Attendance when the Dates are placed in Columns

So here is the scenario: We want to get the weekly average of employee attendance in the office, be able to classify them according to how many days they went to the office (e.g. 0 day, Less than 1 day, 1 day, etc.), and be able to filter according to date, business unit, and other employee details.

 

The data that I have right now are separated into two: first is the Employee Details table, and the raw Attendance table. I mainly just use excel and merge the attendance table to the Employee Details table, so it would like a bit like this:

 

Employee IDBusiness UnitGradeGeneration1/2/20231/3/20231/4/20231/5/20231/6/2023Week 1 Sum1/9/20231/10/20231/11/20231/12/20231/13/2023Week 2 Sum
ALegal5Gen X100102111115

 

In order to get the Weekly Average of all the Week X Sum columns, I have a Weekly Avg column at the end of the table, which is just =average(all Week X columns). The tricky part is when I try to unpivot this table by using the Weekly Avg column as the Attribute, I no longer have the date/week references which means that I could no longer filter them by date. 

 

I tried unpivoting by using the Week X Sum columns and appended a Calendar table to get the dates associated with each week, but the resulting table doesn't filter by date properly and I also don't have decimal points in the Value (which means I wouldn'tbe able to get the Less than a day classification since the Week X Sum Columns are just whole numbers of 0-5)

 

I also tried the Group By function, but it all ended up with the same result above.

 

Hoping that I would be able to get some answers. I've been at this for a month and I'm all out of options

1 REPLY 1
lbendlin
Super User
Super User

I tried unpivoting by using the Week X Sum columns and appended a Calendar table

That sounds vaguely like a good idea.  

 

Please provide sample data that FULLY covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!