• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pivot table filtering - % of entire total not just filtered values

R

roy007

New Member
I have been working around this, but I thought I would try to see if there is a native solution I'm not aware of.


I'm using Excel 2007 and I am categorizing data as follows:


# of payments missed # of loans

Zero 14

One 3

Two 2

Three or more 1

TOTAL 20


Delinquency is calculated as the respective category divided by the total. In this example, 15% of loans have missed one payment (3/20). When presenting this data, particularly in pivot chart form, the percentage of loans that have not missed a payment (70%) skew the scale. In reality, nobody wants to see the 70% figure...they want to see the other 30%. When I filter the pivot table to exclude the "Zero" category, however, the resulting percentages are then recalculated based on the new filtered total. Long question short...is there a way to have Excel calculate these percentages based on the unfiltered total even though I only want to display the filtered/selected items in the pivot chart?
 
Roy

The only way I can see to do that is to select the Zero Row and either

1. Hide the whole Row or

2. Make the Text Font Color the same color as the background


You can do the same to the individual data in a chart, just make the Bar Color 'No Color'
 
Roy,


I think about adding a % columns base on the total line.

then Use this Column instead of the nb one.


HTH


Cyril.
 
Thanks Cyril. How do I do that? If I add a % column in the typical way it is still a % of the filtered total. How do I make it a % of the unfiltered total?
 
It depends if TOTAL is automaticcally calculated by EXCEL (groups ans subtotal) or not.


Best way is perhaps to use a VLOOKUP to retrieve total when calculating %.


Look at the following table

[pre]
# payments # loans %
Zero 14 =B2/VLOOKUP("TOTAL";$A$2:$B$6;2;FALSE)
One 3 =B3/VLOOKUP("TOTAL";$A$2:$B$6;2;FALSE)
Two 2 =B4/VLOOKUP("TOTAL";$A$2:$B$6;2;FALSE)
Three + 1 =B5/VLOOKUP("TOTAL";$A$2:$B$6;2;FALSE)
TOTAL =SUM(B2:B5)
[/pre]

Hope this help
 
You must log in or register to reply here.
Back
Top