Solved

Pivot table percentages

Posted on 2014-11-05
5
160 Views
Last Modified: 2014-11-07
I have source data that shows 3 paths (Path 1, 2, 3), yet when it displays percentages, I only want the pivot table to reflect percentages for only Path 1 and 2. I can hide path 3, but the percentages still count the path 3 numbers.
 How can I solve this?  *See attachment

Also how do I eliminate the #DIV/0! error?

Thanks for your tremendous assistance.
Path-EX.xlsx
0
Comment
Question by:saved4use
  • 4
5 Comments
 
LVL 7

Expert Comment

by:tomfarrar
ID: 40425245
Here is how to get rid of the errors.  Two options I see:

Pivot Table Menu: Options>Layout & Format > Format > For Error Values Show "0"
Or go to your calculated field PKG_% and surround the formula with IfError(Calculated Field), "')
0
 
LVL 7

Accepted Solution

by:
tomfarrar earned 500 total points
ID: 40425269
Look at the Calculated Field In the Pivot Table of Attached.
Path-EX2.xlsx
0
 

Author Comment

by:saved4use
ID: 40426539
@tomfarrar It seems the calculated field produces the perfect percentages when it's only 7 rows of data and NOT when it's 52,500 rows of data (as you can see the multitude of 0.00% throughout the attached pivot table). How can the calculated field be tweaked to reflect correct percentages with a larger dataset?
Thank you for your help (we are close).
Path-EXa.xlsx
0
 
LVL 7

Expert Comment

by:tomfarrar
ID: 40429109
Look at the attached and then we can discuss.  See notes off to the right of Pivot Table.
Path-EX2.xlsx
0
 
LVL 7

Expert Comment

by:tomfarrar
ID: 40429115
Sorry wrong file attached.  See this one......
Path-EX3.xlsx
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

805 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question