Pivot Table Results Are Huge

[Douglas Eckert]

Pivot table results (using SUM) are huge - not the numbers displayed in the underlying table. If I use COUNT, they are too small. Please advise.

DOUG in Wichita KS

 

[RoryA]

Need a bit more to go on than that. PT results are correct as a rule, even if they are not what you expect.

 

[Douglas Eckert]

The results which appear in the pivot table are not the numbers which are found in the source table. They should be displaying scores in the 90 - 100 percent range. Instead, they are displayed as 1800 plus in the pivot table. I have used pivot tables for years and I have never experienced this. Maybe I am having writer's block today or something. I have a slide to show you, but it is on my PC and not posted at a URL...

 

[RoryA]

If you double-click one of the values to bring up the detail sheet, are the numbers there correct?

 

[Douglas Eckert]

Expected results would have been in the range of 90 to 100 percent, i.e., matching the scores in the source table. But, they are on the order of 1800 plus when I use SUM in the Field Settings. If I use COUNT, all of the results are the number 21. I have used pivot tables successfully for many years, but I have never experienced this problem. I am trying to display metric scores in a pivot table. The table is fine. Clinic names are displayed as row headings and months are displayed as column headings. But, the numbers are all wrong.






douglas.eckert.1@us.af.mil

 

[RoryA]

If you have 21 entries in the 90-100% range and you sum them, you will get something in the 1800%+ range, since all it does is add up the percentages.

 

[Douglas Eckert]

I guess I need to know why it is multiplying these items together instead of just displaying the scores (?)

 

[RoryA]

It's not multiplying, it's adding. If you have 21 values of 0.9, then the total is 18.9 or 1890%. What are you expecting it to return? (if you want an average, use that, but it won't be weighted, if that's important)

 

[Douglas Eckert]

I am expecting it to produce a neat little table like it has in the past. Column A would contain the names of clinics in our company, field headings would be the months and monthly scores would be displayed in the columns. I have used the SUM option before to produce this kind of pivot table, although I was not really summing anything, just displaying the data. On another related website I was just looking at a tutorial which showed an example of the same thing I am trying to do, using the same steps. (I wanted to send you the link, but the page has closed now). It shows the academic grades for two students in the source table. The pivot table uses their names as a filter. The names of their classes are in column A, the months are the field headings atop the columns. The columns display their grades. The grade scores are not being added or acted upon, just displayed in the pivot table. I am attempting to do the same thing using metric scores.

 

[RoryA]

It would be helpful to see your data layout. If you are getting a count of 21 for each student, then it appears you have 21 items being aggregated. On that basis, you cannot simply display the scores. Why do you have 21 items per student?