Pivot Table: Calculated Fields % of grand total in an equation

[Mohamed AbdelAziz]

Hi, hope you are good.

I have a pivot table like the example and the the third column is in show value as % of grand total.
I want to create a new column that multiply the % Total Hours and the the Rate.

[TABLE="width: 498"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Total Hours[/TD]
[TD]% Total Hours[/TD]
[TD="align: center"]Rate[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD="align: right"]05:00[/TD]
[TD="align: right"]29.41%[/TD]
[TD="align: right"]69[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD="align: right"]02:30[/TD]
[TD="align: right"]14.71%[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD="align: right"]02:30[/TD]
[TD="align: right"]14.71%[/TD]
[TD="align: right"]48[/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD="align: right"]07:00[/TD]
[TD="align: right"]41.18%[/TD]
[TD="align: right"]96[/TD]
[/TR]
[TR]
[TD]e[/TD]
[TD="align: right"]00:00[/TD]
[TD="align: right"]0.00%[/TD]
[TD="align: right"]69[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD="align: right"]17:00[/TD]
[TD="align: right"]100.00%[/TD]
[TD="align: right"]76.4[/TD]
[/TR]
</tbody>[/TABLE]

 

[pbornemeier]

For Excel 2010 (and probably 2007 and later)
Select any cell in PivotTable
In the menu select: PivotTable Tools | Options | Fields, Items & Sets | Calculated Fields
The Insert Calculated Field dialog box will be displayed.

 

[Mohamed AbdelAziz]

I'm sorry you didn't get my question.
I know how to make a calculated fields, the problem is when i multiply 2 columns and one of them is in "show value as % of grand total" format. the results are wrong. Like this example:

[TABLE="width: 579"]
<colgroup><col><col span="4"></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]Total Hours[/TD]
[TD]% Total Hours[/TD]
[TD]Rate [/TD]
[TD]Sum of Field1[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD="align: right"]05:00[/TD]
[TD="align: right"]29.41%[/TD]
[TD="align: right"]69[/TD]
[TD="align: right"]14.375[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD="align: right"]02:30[/TD]
[TD="align: right"]14.71%[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]10.41[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD="align: right"]02:30[/TD]
[TD="align: right"]14.71%[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD="align: right"]07:00[/TD]
[TD="align: right"]41.18%[/TD]
[TD="align: right"]192[/TD]
[TD="align: right"]56[/TD]
[/TR]
[TR]
[TD]e[/TD]
[TD="align: right"]00:00[/TD]
[TD="align: right"]0.00%[/TD]
[TD="align: right"]69[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD="align: right"]17:00[/TD]
[TD="align: right"]100.00%[/TD]
[TD="align: right"]478[/TD]
[TD="align: right"]338.583[/TD]
[/TR]
</tbody>[/TABLE]

I want the results in the last column equal to 29.41% * 69 = 20.29

 

[Mohamed AbdelAziz]

Thank you guys i got it.

When you write your equation in the Calculated Fields you add "%" after the column name, example:

= 'Rate'*'Total Hours'%


This will do it for you.

 

[Mohamed AbdelAziz]

This didn't work also.