Sum Issue On Table Footers

I am having a big issue trying to resolve this problem. Hope to layout it out well enough to understand.

I have a dataset like this.

Name Sales$ Quota Month
-----------------------------------------------
John Doe 10 10 1/1/2006
John Doe 20 10 1/1/2006
John Doe 15 10 1/1/2006
John Doe 18 10 1/1/2006
-----------------------------------------------
Jane Doe 12 5 1/1/2006
Jane Doe 6 5 1/1/2006
-----------------------------------------------
Jack Black 18 15 1/1/2006
Jack Black 22 15 1/1/2006
Jack Black 14 15 1/1/2006
-----------------------------------------------

The issue I am having is quota is truely only the one value, not a sum of the quota. So John Doe should have a quota of 10, not 40. In the report layout, I simply do a max or first to get the names quota. The issue is doing a grand total in a table footer. When you do a sum, you of course get a sum of each value, not a sum of the distinct values for each name (which is the grouping of the report).

If you add all the total quota up we get 95. If I take a count of quota you get 9. Divide 95 by 9 you do not get the correct quota total. I need to get a total of 30 for quota. If I take a count of each name, and divide the total quota for that group by the count...I get the correct number.

Example: John Doe would total to 40 for quota...divide by 4 you get 10...which is correct

I would then need to total all these grouping totals up for a grand total in a Table footer (not a group footer, but a table footer).

I have tried runningvalue to no avail. Tried using reportitems! but cannot do a sum in a report footer for reportitems!.


Any thoughts? I was hoping I could use reportitems! to reference the cell name, but as I have seen in many posts this is not possible in a table footer.

Any help or push in the right direction would be wonderful. If this needs further clarification, please let me know.

95


95