Pivot Table and Chart Issues

[DanC14055]

Hello All,

I am having a problem that I am sure can be easily fixed, I just can't figure it out!!

I am trying to make this pivot table

[TABLE="width: 845"]
<colgroup><col><col><col span="3"><col><col span="7"><col><col span="3"><col><col span="3"></colgroup><tbody>[TR]
[TD]Count of Rating[/TD]
[TD]Column Labels[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2012[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2013[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row Labels[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]Oct[/TD]
[TD]Nov[/TD]
[TD]Dec[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: right"]11%[/TD]
[TD="align: right"]4%[/TD]
[TD="align: right"]3%[/TD]
[TD="align: right"]8%[/TD]
[TD="align: right"]6%[/TD]
[TD="align: right"]7%[/TD]
[TD="align: right"]12%[/TD]
[TD="align: right"]10%[/TD]
[TD="align: right"]9%[/TD]
[TD="align: right"]10%[/TD]
[TD="align: right"]12%[/TD]
[TD="align: right"]11%[/TD]
[TD="align: right"]11%[/TD]
[TD="align: right"]13%[/TD]
[TD="align: right"]12%[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]13%[/TD]
[TD="align: right"]13%[/TD]
[TD="align: right"]12%[/TD]
[TD="align: right"]12%[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]7%[/TD]
[TD="align: right"]4%[/TD]
[TD="align: right"]8%[/TD]
[TD="align: right"]13%[/TD]
[TD="align: right"]6%[/TD]
[TD="align: right"]7%[/TD]
[TD="align: right"]8%[/TD]
[TD="align: right"]8%[/TD]
[TD="align: right"]10%[/TD]
[TD="align: right"]8%[/TD]
[TD="align: right"]10%[/TD]
[TD="align: right"]11%[/TD]
[TD="align: right"]8%[/TD]
[TD="align: right"]11%[/TD]
[TD="align: right"]11%[/TD]
[TD="align: right"]11%[/TD]
[TD="align: right"]11%[/TD]
[TD="align: right"]10%[/TD]
[TD="align: right"]10%[/TD]
[TD="align: right"]12%[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: right"]12%[/TD]
[TD="align: right"]10%[/TD]
[TD="align: right"]10%[/TD]
[TD="align: right"]7%[/TD]
[TD="align: right"]11%[/TD]
[TD="align: right"]11%[/TD]
[TD="align: right"]9%[/TD]
[TD="align: right"]10%[/TD]
[TD="align: right"]10%[/TD]
[TD="align: right"]10%[/TD]
[TD="align: right"]12%[/TD]
[TD="align: right"]10%[/TD]
[TD="align: right"]10%[/TD]
[TD="align: right"]11%[/TD]
[TD="align: right"]11%[/TD]
[TD="align: right"]10%[/TD]
[TD="align: right"]10%[/TD]
[TD="align: right"]10%[/TD]
[TD="align: right"]10%[/TD]
[TD="align: right"]10%[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: right"]18%[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]14%[/TD]
[TD="align: right"]20%[/TD]
[TD="align: right"]19%[/TD]
[TD="align: right"]20%[/TD]
[TD="align: right"]17%[/TD]
[TD="align: right"]18%[/TD]
[TD="align: right"]18%[/TD]
[TD="align: right"]22%[/TD]
[TD="align: right"]19%[/TD]
[TD="align: right"]19%[/TD]
[TD="align: right"]20%[/TD]
[TD="align: right"]17%[/TD]
[TD="align: right"]19%[/TD]
[TD="align: right"]19%[/TD]
[TD="align: right"]21%[/TD]
[TD="align: right"]20%[/TD]
[TD="align: right"]19%[/TD]
[TD="align: right"]20%[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD="align: right"]51%[/TD]
[TD="align: right"]68%[/TD]
[TD="align: right"]66%[/TD]
[TD="align: right"]52%[/TD]
[TD="align: right"]58%[/TD]
[TD="align: right"]54%[/TD]
[TD="align: right"]53%[/TD]
[TD="align: right"]55%[/TD]
[TD="align: right"]53%[/TD]
[TD="align: right"]50%[/TD]
[TD="align: right"]47%[/TD]
[TD="align: right"]49%[/TD]
[TD="align: right"]51%[/TD]
[TD="align: right"]49%[/TD]
[TD="align: right"]46%[/TD]
[TD="align: right"]45%[/TD]
[TD="align: right"]46%[/TD]
[TD="align: right"]48%[/TD]
[TD="align: right"]49%[/TD]
[TD="align: right"]46%[/TD]
[/TR]
</tbody>[/TABLE]

Into a stacked column chart as they all add up to 100%. But it just keeps coming out all goofy because its a pivot table. I don't want to hard code the data because I will be updating this regularly and want the chart to update as well.

Thoughts??

 

[ChrisM]

Are you using a chart or a pivot-chart? pivot charts are always linked to the underlying pivot table and will always stay updated. There is an option to insert one right on the Pivot Table ribbon. You will need to move your dates to the rows section (not the column as you have it).

 

[DanC14055]

Ah hah! Thanks yes, I switched the rows and columns and made the percentages now a % of the row total instead of columns and it worked!

Thanks I knew it was a simple fix.

 

[DanC14055]

Okay, so that worked... But! Now I am trying to have a line graph and stacked column combo. The line graph would be the average of all of the ratings and the stacked column would be the percentage of each type of rating. I can't see, to get this to work either.

 

[ChrisM]

what does your raw data look like? Is it essentially just:

Rating (1-5), Date

Then your pivot table just does a count of those ratings?

 

[DanC14055]

Yes more or less. There are a bunch more fields and thousands of lines of data, but for this pivot table I am only using date and rating.