Problems with percentage totals in columns in pivot table report

[Zyphon]

I am running a report for attendance from raw data pulled from our database. I need to be able to check percentages over a week and compare them for Attendance and non-Attendance.

I have attached a screenshot with shows what I am trying to achieve.

The class is Basic_Accounting and from the week commencing 08/04/2013 to 14/04/2013 there was a maximum potential of attendance of 5. In this case 4 attending and one non-attendance.

Now if you look at my manual calculation on the right hand side you will see that it calculates correctly that the 4 attendees for that week amount to 80%.

Now I have added a count of attendance type in the values field but when I add the count field for percentages and select % of column it seems to be calulation a $ of the grand total and not the 5 in the column.

I can get around this by moving the attendance type from Row Labels to Columns labels and change the field setting to % of row.

Is there a way I can get the correct percentage total per week?

Thanks.

[judgeh59]

Hi Michael -

Is it possible to upload some raw data to play with?....no company confidential stuff, of course....thanks

[Zyphon]

Hi Ernest,

Many thanks for your offer of help.

I have attached a spreadsheet with some sample data and the sensitive information removed.

I have 2 pivot table reports, The Report by column has a second count function with % of column. The second Report; Report by row has a second count function of % of row. I have highlighted the correct calculation for the percentage of attendance ( the letter 'A' in this case). In the Report by column I get 0.45% instead of 87.5%. I have included a manual calculation to the right of this report to illustrate the returned values that I require.

I hope I have explained myself clearly and not confused anyone. Thanks in advance for any help offered.

[judgeh59]

I don't know if your tried different choices in the "show values as" drop down....I think I got what you want using the "% of parent row total"...hopefully you have that as a choice in 2007 and it does what you want....good luck