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 ID | Business Unit | Grade | Generation | 1/2/2023 | 1/3/2023 | 1/4/2023 | 1/5/2023 | 1/6/2023 | Week 1 Sum | 1/9/2023 | 1/10/2023 | 1/11/2023 | 1/12/2023 | 1/13/2023 | Week 2 Sum |
| A | Legal | 5 | Gen X | 1 | 0 | 0 | 1 | 0 | 2 | 1 | 1 | 1 | 1 | 1 | 5 |
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
