I'm working on Payroll data and I'm stuck trying to determine the % of the year worked per employee. My table name is 2021
I created these measure to determine the number of payroll periods in the year:
MINDate
2021MINdate = MIN('2021'[Date])
MAXdate
2021MAXdate = MAX('2021'[Date])
2021Weeks
2021Weeks = DATEDIFF([2021MINdate], [2021MAXdate], WEEK)
Number of Payroll periods
2021NumofPayroll = DIVIDE([2021Weeks], 2)
I get 25 as the number of payroll periods based on my 2021 payroll data.
When I use 2021NumofPayroll measure in tabular form along with the division and name of employee, it gives me the number of payroll periods each employee worked that year.
Where I've run into a problem is that I would like to take that info and divide it by the 2021NumofPayroll (which the aggregate is 25) so that I can get the % the employee worked, but when I create another measure it gives me 1 for each employee.
I somewhat understand that PBI works in a row fashion so I understand why it's giving me the result of 1.
FYI - this data contains payroll codes so any COUNT expression would be tricky because one employee could have multiple paycodes during one payroll period.
Questions:
- What expression do I need to use in order for it to be able to calculate the # of pay periods by emp so that I can get the % for the year? For example, John worked for 5 pay periods of 25, the measure should calculate 25%.
- I'm also having issue bringing in a column from a related table that shows the number of employees allocated by division. The relationship from my payroll data to the related table is a many to one. I've tried having the relationship go from the related table to the payroll table so it's a one to many but PBI keeps changing. I don't know if that makes a difference or not. Anyway, I want to take the number of employees from the related table to show what the full capacity of the division should be, so that I can compare to the ACTUAL capacity during the year, which accounts from the vacancies throughout the year. My issue is when I go to create an expression to get the percentage, I use RELATED in the payroll data set but it doesn't recognize the table that contains the employee count by division.
I'm presenting this to leadership this coming Tuesday (8/15) so any help is appreciated.
Thank you!
Linda
