Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2012-08-15
Medium Priority
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
  • 2
  • 2

Expert Comment

ID: 38298905
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

ID: 38298924
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.

Accepted Solution

5teveo earned 1500 total points
ID: 38314612
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

ID: 38314748
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.

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

864 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