Forum Discussion
Excel Calculated field in pivot
Hi Excel Gurus,
I need help to solve this scenario.
My Raw data has columns MS2010
Name Lead Break Lunch BioBreak
Abhishek Amit Pankaj 0:00:00 0:00:00 0:00:00
Kreetika Amit Pankaj 0:19:31 0:33:06 0:00:00
tiwari Amit Pankaj 0:00:00 0:00:00 0:13:28
Kreetika Amit Pankaj 0:00:00 0:00:00 0:13:53
Sharma Amit Pankaj 0:04:30 0:33:46 0:00:00
tiwari Amit Pankaj 0:00:00 0:29:22 0:05:26
subham Amit Pankaj 0:00:00 0:30:25 0:00:00
sumit Amit Pankaj 0:19:09 0:33:44 0:08:59
I made calculated columns - all break which is a sum of all three breaks and worked properly
now i made a another calculated column with elapsed time =IF((HOUR('All breaks' )-1)<0,0,SUM(('All breaks' )-TIME(1,0,0)))
I get the right elapsed time when i look with each names but the subtotal is giving incorrect answer ,
can anyone help me fixing this , much appreciate your response.
Sam
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.
