Calculated field in Pivot

[kansaskannan]

I have the following data in a pivot table.

a) Account Type (4 types of accounts)
b) Loan amount
c) Delinquent (payment past due date = yes/no)
d) Month, year

The data looks like this.
===================================================
LoanDate Delinquent Account
LoanAmount
===================================================
Feb-05 NO Type1 409
Aug-05 NO Type1 1260
Nov-05 YES Type2 2550
Jul-05 NO Type3 1936
May-05 NO Type4 2943
Feb-05 NO Type1 198
Apr-05 NO Type1 2309
Apr-05 NO Type1 1815
Jan-05 NO Type4 2004
Jan-05 NO Type1 1975
Jan-05 YES Type2 1516
Jan-05 YES Type1 71
Feb-05 NO Type2 191
===================================================

I have the month/year as a row field, Account Type as a column field,
and Delinquent as a page field. The data field is either dollar
amounts or count of loans.

In the pivot table, I would like to include a field that shows
Delinquent loans as a percent of all (delinquent and non-delinquent)
loans for each Account Type. How would I do this? Any suggestions
would be welcome.

Thank you very much.

p.s. I went through Debra Dalgleish's book Excel Pivot Table Recipe
Book, and the section on calculated fields, but could not figure it
out.

 

[Jim Thomlinson]

Depending on whether you are willing to change your format this can be done
using a field setting option.

Column field -> Account type
Row field -> Delinquent
Data field -> Amount

Right click on the Data field and select
Field Settings -> Options -> Show Data As ->% of Column...

Sum of Amount Deliquent
AccountType YES NO Grand Total
Type1 1.72% 52.97% 41.91%
Type2 98.28% 1.27% 22.20%
Type3 0.00% 12.87% 10.10%
Type4 0.00% 32.89% 25.80%
Grand Total 100.00% 100.00% 100.00%

 

[kansaskannan]

Jim,
Appreciate your taking the time to write. Unfortunately I do need to
have the period (month/year) as part of the table. What I plan to do,
is to create two pivots with 'Delinquent' as the page field. In first
pivot the page field is set to "YES" and in the second pivot page
field shows both YES+NO. I will then write some VBA code to create a
'replica' of the pivot and compute the percentages.

Thanks again for your help - your earlier suggestion on including
optional page fields is working great!

kannan