Calculating row wise Average instead of Total in the Pivot Table?

[BeuranHendri]

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.

[Digvijay_Singh]

Not sure but what if you add Avg(Aggr(current expression,Chart dimension)), as I recall the total property is not a drop down in the pivot chart but just want to check if above modification makes any difference..