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
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
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
Sounds like a grouping problem to me...
more... can you code you data inside SQL to group better for pivoting?