FileMaker - Total SubSummary Values

I have a table with records each representing an appointment. I have the name of the contactthe appointment is with, and the date. In another table I have a field that contains how many appointments each contact is supposed to have during the day. There are 12 entries for each contact, because some are expected to have different numbers during different months.

I am able to call up the data for the appropriate contactfor the appropriate month. It looks great in the graph when I count up the number of entries for Contact A and put next to it the expected number of entries from the related table.

The problem I'm running into now is that I need to add up all of the expected appointments between all of the entities. So:

::ContactName::  ::appointments::       ::expected::                                  
Contact A              12                   10
Contact B              33                   34
Contact C              18                   27

Getting the roll up for the actual appointments is easy, a simple COUNT summary field in a subsubsummary section. But what of the expected? Because ContactA had 12 appointments that means that there will be 12 records for them, so putting a summary field for the expected column is would return 120 for all Contact A's. Instead, given the dataset above, I need the calculation to return 71. Does this issue make sense? Any help would be greatly appreciated.

Solution

If I am following this correctly, you need to divide the amount of expected appointments between the entries of the group, then total the result. So something like:

Sum ( Entities::Expected ) / GetSummary ( sCount ; EntityID )

(this would be easier if we knew the names of your tables and fields).

P.S. The term "entity" has a specific meaning in the context of a relational database. Consider using another term (e.g. "contacts").

Added:

Using your example data, you should see the following results in the above calculation field:

in the 1st group of 12 records: 10 / 12 = .8333333333333333
in the 2nd group of 33 records: 34 / 33 = 1.0303030303030303
in the 3rd group of 18 records: 27 / 18 = 1.5

When you sum all this up (using a summary field defined as Total of this calculation field), you should get 71 (or a number very near 71, due to rounding errors).

Note: in the above calculation, sCount is a summary field defined in the Appointments table as Count of [ any field that cannot be empty ], and EntityID is the field by which your records are sorted and grouped (and it must be a local field).