Calculated field in Pivot
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%
--
HTH...
Jim Thomlinson
" wrote:
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.
|