Can a Pivot Chart selectively exclude data in the Pivot Table? NO ONE HELPED ME ... :o(

Hello again:

I've attached a sample doc to show what I'm talking about. In the data, there are 4 colours of balloons and how many are sold each day. My real data set is far more complex, and there is no grand total for rows in the very large data set as I receive it. Similarly there is no grand total for rows in my example sheet.

I created a pivot table and pivot chart in my example doc.

First question: why doesn't a grand total for the rows show up in my pivot table? I have that option checked, but it doesn't total the rows.

Second question: I would like to chart the % of total sales per day of each colour, but exclude the red balloons. I need the red balloon data in the table in order to calculate the total number of balloons sold each day, but in my chart I don't want to see the daily % for red balloons - just the blue, green and yellow ones. Basically, I'd like the data for red balloons to be excluded from the chart, but not from the table.

Can someone help me with this?

Thanks!

Hi!
#1:
I believe Your 'source data' in A1:E12 needs to have a total column. If your true source data doesn't have a total row (and you're not able to manipulate that, perhaps you could keep that data on sheet1 and make a lookup on sheet2 that does have a total? Then all you do is refresh your pivot table and take the now available 'total' column and plunk in into the 'values' section on the pivot table field list chooser. A second option would be to create a calculated 'total' field in the pivot table (=BLUE +'GREEN ' +'YELLOW ' +RED); however that is REALLY annoying if you have a LOT of columns of data to 'sum'.

#2:
The issue may be that you created a Pivot CHART right from the beginning; that leave the data ALWAYS linked as far as I know. I typically prefer to just create a Pivot TABLE to get all my data; then create a separate CHART that can look up whatever I want...I simply get it to look up the range of my pivot table (by highlighting it). Then my chart has data filters that are separate from the pivot table. That will allow you to have different data on the table and the chart.

I know this was forever ago and your issue is most likely resolved; but perhaps someone else can benefit from this info. Thanks! :-)