• 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 Issue

P

phoward

New Member
I am attempting to get the "% of total" in the column after "Grand Total" in my pivot table. I cannot use a calculated field (at least it hasn't worked yet) and so far this process has been manual.

Here is the issue: this pivot table is a rolling 3-quarter so once another month is thrown in the "% of total" disappears and also as new "parts" are added, the "grand total (or 100%) row has to be moved and the entire "% of total" column re-calculated to reflect the additional item in column A.

Any help would be appreciated. I've uploaded a file as an example. Thanks!
 

Attachments

Welcome to the forum, phoward!
Next time, with a PivotTable question, it helps if your example has an actual PivotTable. :)

You can achieve your outlook by adding the value field a 2nd time to the Data area. We then right-click on that field, and choose "Summarize values by...", more options, Show Value as, % of Grand Total.
In the attached, I changed the name of the field for clarity. Then, hide columns C and E, and we're done. :cool:
upload_2014-12-5_10-40-0.png
 

Attachments

Hi Luke. Thanks for the welcome and also "words of wisdom". Next time, I will include all data. Are there any other solutions? The reason I ask is that this pivot table is on a page with 7 other pivots and corresponding charts, so hiding columns causes an issue. Maybe the entire dashboard needs a "redesign"!
 
Yikes, that's a lot of PivotTables(PT). I hope all the PTs are linked as a common source...

If not too much trouble, it would probably be better to redesign your layout a bit. Since PT's tend to grow/shrink dynamically as data is added/removed, having multiple items on a single sheet can get quite tricky. Perhaps you could have all the charts and some slicers linking to PTs (stored on a separate tab) all on your front dashboard?
 
That is a wonderful idea Luke except management HATES change and they like the dashboard as is. Now, what I do intend to do is eliminate the need for 8 sheets containing all the different regions and educate them. The dashboard was originally created by an outside consulting firm. I loathe it. I'm making small changes a little at a time until it can finally be "updated" altogether. You know, not all at once so I don't cause a complete upheaval. In the meantime, I would like to insert a macro button to update the dashboard (the overview page, all eight regions and the originating line of business pages) but I can't really do that until I fix this "% of total" issue that is giving me fits. Does it appear that redesign is the only option to you?
 
Ugh, sounds like you work at the same company that I used to. :P

My thought process:
The % of Total needs to be a part of the PT, or you end up doing it manually. This is where you are currently.
a) If we add the desired column to PT, it will create an additional column for each month. We can either leave this showing (possible??), or try to hide
b) If we need to hide, this could interfere with other items above/below current PT. Can we use a picture link? User can't click on the PT to change things, but get to see what they need w/o messing up layout
 
They did not like seeing the additional columns at all - said was too much info :confused:. They do like the pivot tables because it affords them the advantage of double clicking to get the customer information, etc that makes up the number so the picture link was also not an option. I don't quite understand why tables and charts are needed for every portion of the reporting. Personally, my thought is that "some" of the pivot tables can come out and more information can be added into the charts (such as formulas as legend entries to show increase/decrease % in comparison to previous month and data labels for dollar figures). This would eliminate the pivot tables that (IMO) are unnecessary (3 of the 8), resulting in the possibility of hiding those columns and an easy restructure of the dashboard. I might just try it that way if there are no other options for getting this formula to work. I am not new to excel but also am not familiar with some of the more advanced skills like extensive VBA. My limit to that is recording a macro. I thought that may be a possibility and have been studying up on it without much luck in finding the solution on my own. Guess it's time to start rebuilding! ;)
 
As a last resort, you/we could write a macro that redoes all the formulas that you are currently doing manually. But I think as you're realizing now, that would simply be attacking a symptom, not the cause. Agree with you about the tables + charts. A good chart shouldn't need a table of numbers next to it, otherwise one of them is redundant.
 
You must log in or register to reply here.
Back
Top