Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello wizards,
I have a requirement in which I need to display employees full time equivalent (FTE) hours per month for a 13 month period. I developed the report using a pivot table and everything seems fine except the Total column (I enabled show Totals). The user wants to see the average of the FTE hours instead of the Total column.
I created a measure to calculate the FTE hours using the below expression:
If(Getselectedcount(TimePeriod)=0,
Sum({<MTD_HRS-={0}, [TimePeriod]={">=$(=Date(Addmonths(Today(),-12),'MMM-YYYY'))<=$(=Date(Today(),'MMM-YYYY'))"}>} MTD_TOT_HRS)/STD_REG_HRS,
Sum({<MTD_HRS-={0}, [TimePeriod]={">=$(=Date(Addmonths(Max(TimePeriod),-12),'MMM-YYYY'))<=$(=Date(Max(TimePeriod),'MMM-YYYY'))"}>} MTD_TOT_HRS)/STD_REG_HRS)
Field description:
TimePeriod = month-year data
MTD_TOT_HRS = total hours worked in month
STD_REG_HRS = total hours an employee is supposed to work in a month (fixed value)
EMP_NAME = Employee Name (in pivot chart)
EMP_NUM = UPI (in pivot chart)
Sub Functional Area = Sub Functional Area (in pivot chart)
I also tried to calculate average FTE hours expecting to see the average value in the Total column, but then values in Total column dont match with the expected avg values.
I am unable to figure out how to implement this. Kindly assist me with this requirement.
The data looks like this in the front end: