Pivot - Calculated field

[sanshah01]

Hi,

I have a database of the following columns based on which a Pivot is
created. There are various other parameters/Columns by which Sales,
Margin are broken down. Each month may have hundreds of lines.

The below is just a simple overview.

Period Account Customer Type Country Amount
Jan Sales x A TK 10
Jan Margin x A TK 3
Jan Sales y B DF 25
Jan Margin y B DF 15
Feb
Mar

The Pivot layout is in the following way

Page : Period - This enables the user to choose the period for Sales
and Margin.
Row : Country + Customer + Type
Column : Account
Data : Amount

I have add Margin% as a calculated field (Margin/Sales).

The problem is :

1. How do I format it as a % within the pivot so that even if the pivot
is changed, the %format stays. Currently the number format is
"General".

2. In the Margin% column, the Total taken by the Pivot by type or by
customer is the total of the percentages mentioned above rather than
actually calculating the percentage of the total margin and total
sales. This gives incorrect percentages. How do I avoid that.

Regards
Sandip.

 

[Debra Dalgleish]

1. Set the pivot table to preserve formatting:
On the pivot toolbar, choose PivotTable>Table Options
Add a check mark to 'Preserve formatting', click OK
Remove the check mark from AutoFormat Table
Click OK

You may have to enable selection (From the Pivot toolbar, choose
PivotTable>Select, and click on Enable Selection)

Before formatting cells, use the selection feature to select the
cells. For example, move the pointer to the top of a column in the pivot
table (just above the column's heading cell). When the black arrow
appears (like the one that appears when the pointer is over a column
button), click to select the column in the pivot table. Then apply the
formatting.

If the above suggestions don't work, you could record a macro as you
refresh and reformat the pivot table. Then, run that when you want
to update.

2. You can't change the function used in the grand total for the
calculated item. You could format it with white font, so it's not visible.

Or, instead of a calculated item, you could use a custom calculation. In
a backup copy of the workbook, try the following:
Delete the calculated item from the pivot table.
Add a second copy of the Amount field to the data area.
Right-click on the heading for the new field
Choose Field Settings
Click the Options button
For 'Show data as', select '% Of'
For the Base Field, choose Account
For the Base Item, choose Sales
Click OK