Excel Calculated field in pivot

Hi Satish,

Power Pivot calculates incorrectly Grand total on calculated columns. See, for example, https://support.microsoft.com/en-us/help/211470/calculated-field-returns-incorrect-grand-total-in-excel

In your case Grand total for Elapsed time actually is calculated as Grand total for all breaks minus one hour.

The workaround could be to add the column which calculates elapsed time into your source table like

=MAX(0,SUMPRODUCT(($A$2:$A$9=A2)*($C$2:$C$9+$D$2:$D$9+$E$2:$E$9))-1/24)/COUNTIF($A$2:$A$9,A2)

and add it into the Pivot Table.

Please see attached.