Averaging the Averages
When working on a sub-summary report in FileMaker recently I had the occasion to use a feature I'd not used before.
The report is for a client that does after-school programs. The client wanted to get an idea of the average number of students in a class per week of the month. And they wanted those totals and averages by school, and then by type, and then by area. That means there can be as many as three sub-summary parts in this report. In each sub-summary part for each week there are three numbers: total classes, total students and what in essence is an average of the averages.
That's where the fun starts. If you merely take the average of the averages you get a skewed number. You see from this screen shot that the average number does not equal the total number of students divided by the total number of classes, which is the value we need. The varying number of classes makes the averages that represent more classes less significant than they should be in the "average of the averages."
Notice that the average for week 2 is shown as 8.8, but if we had taken the total number of students and divided by the total number of classes we should have gotten 9.4. Unfortunately, you cannot just put a regular calculated field into a sub-summary part. That doesn't work either. Clearly we need some way to take into account the varying number of classes that are included in each of the averages that we are averaging.
The solution? There is a button in the field options dialog when you are setting up a summary average field that allows you to "weight" the averages. In this case the correct approach is to weight the averages by the number of classes. That way the averages that represent more classes get more "weight." As you can see the answer comes out correct now:
Credit goes to my buddy Byron Songer for pointing this seldom-used (by me anyway) feature out to me.
