Pivot table show values - what's the difference between %of row total and % of parent row total

Posted on 2012-08-15
Last Modified: 2012-08-20
Hi All,
I have a pivot table that gets its data from an SQL server SQL connection.
It's sales information, grouped by month and different items.

I have set up the pivot table to show me the percentage of sales for each different item
e.g January - 25% CDs, 50% books, 25% Tshirts
I did this by  showing the summarised values as a '%of row total'
However, if I hide one of the items, it changes the row total, and hence the percentage.

Is it possible to retain the % calculation based on the row total, ignoring any hidden fields?

I thought that might be what the difference between '%of row total' and '%of parent row total' is, but it doesn't seem so.

Many thanks
Question by:adamianf
    LVL 8

    Expert Comment

    can provide spreadsheet and state which data you removed and your expected values? i know it sounds like alot to describe but you can do too much in pivots...
    Sounds like a grouping problem to me...

    more... can you code you data inside SQL to group better for pivoting?

    Author Comment

    Thanks for your response.

                             CD                               LP      
    Row Labels      amount                            %               amount                      %
    01-01-2009      $200,513.70      86.29%      $31,847.15      13.71%
    01-02-2009      $204,157.61      85.14%      $35,631.59      14.86%
    01-03-2009      $190,855.80      85.49%      $32,387.43      14.51%
    01-04-2009      $193,416.34      84.37%      $35,825.40      15.63%

    If I 'hid' the LP format, that would make the CD percentage 100%
    What I want to see is that CDs are 86.29% of total sales, regardless of whether I'm showing the other fields in the pivot table.

    I can definitely re-tweak the SQL, but just wanted to check first to see if there was a way to summarise this in a pivot table.
    LVL 8

    Accepted Solution

    Start here...

    I uploaded an Excel spreasheet with
    1) Your Pivot Data  (Pivot1/Pivot1Data)
    2) Modify Pivot data (Pivot2/Pivot2Data)

    The Pivot table only tallies what is has exposed to user - so to speak. If the user choose to remove a column the Pivot table ignores that data. Its the basic function of pivoting. Hence, you cannot get your %'s based upon date row tally's as you are wanting


    If you include the Total of Date in the returned SQL data as an LP/CD type (See enclosed Pivot2data) then you can compute %'s as of Total column (that must remain exposed) on pivot instead of total for Row.

    OPTIONS for new TOTAL Row...

    Handle creation of TOTAL for date row in SQL
    Handle creation of TOTAL for date row in EXCEL via macro

    Author Comment

    I understand what you mean.
    What  I really needed to hear was:
    "The Pivot table only tallies what is has exposed to user - so to speak"
    That answered my question.

    I re-wrote my SQL to return the data I wanted and it all works fine now.

    Thanks for your help.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
    Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now