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

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
etc
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
adam
adamianfAsked:
Who is Participating?
 
5teveoCommented:
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

BUT

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
or
Handle creation of TOTAL for date row in EXCEL via macro
PivotTableTallys.xlsx
0
 
5teveoCommented:
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?
0
 
adamianfAuthor Commented:
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.
0
 
adamianfAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.