Pivot charts - AutoCalc option grayed out

[kkrishna]

Hi All,



I selected the data from a query to display the average value of 12 months of values for one criteria (e.g Overtime pcnt) for a group of offices on the X-axis. The bar chart shows the Offices on the X-axis and the Y-axis shows the count of the values (in this case 12) for each office. I clicked on the bars to change it to show the Average value - but the only option in the AutoCalc is "Count". The rest of the options are grayed out.
How can I change it to show Average and the number format as Percentage with two decimal places?
Thanks in advance
Krishna

[kkrishna]

Perhaps I need to explain the problem in more detail for a response.
I am using Access 2007.

I have collected the data from the various units for 12 months of 2009. By using a query with calculated fields, I calculated some Performance Indexes and one of the indexes is the Overtime Percentage, expressed in Double upto 4 decimal places.

I opened a Pivot Table view for this query and I want to see the Average value for a selected period of 3 months. The Pivot table displays the Count function only and in the details shows the individual Overtime value for each month. I want to have the Average value in the Totals column, which I am not able to do.

When I select the Show Details for the Overtime data, I only get the values for individual months. When I select the AutoCalc, it gives me the option of Count only - all other options like Average, Maximum, Sum etc are grayed out.

It is so easy in Excel - well someone may suggest to do it in Excel. The point is whether Access has some limitations on these points.

Once I am able to get the Average and other options through the AutoCalc menu in the Pivot table, I want to see how the chart looks like in Pivot chart.

I created a Pivot chart directly from the query selecting the Overtime column. The chart shows the unit names in the X axis and the Count in the Y axis. Since each unit has 12 values for the year 2009, that chart is useless. Unless I change the criteria to Average (or Sum), I cannot get any meaningful report.

I even tried to create the chart directly in a Form from the data in a query. This is giving me some funny labels for X- and Y- axes like East, West etc. I can not make out where these labels are coming from.

I may be covering a lot of ground in this post; but any suggestion on any of the points will be appreciated.

Thanks in advance