Averaging values in a field in multiple records dependent on another field

[bac mac]

In one layout, I'd like to display multiple aggregate information grouped different ways at the same time. For example, oversimplifying the database:

Group-----Image-----Value-----Image Average-----Group Average

0 Glu---------1---------24----------------25---------------------30

0 Glu---------1---------26

0 Glu---------2---------34---------35

0 Glu---------2---------36

3 Glu---------1---------19 etc.

The left three fields represent the input data, and the right two columns the calculations.

I tried setting up relationships and using the Average function but it didn't work.

I can perform sub summaries via parts or summary calculations, but just for the Image Average or the Group Average. If its possible to do simultaneously, I don't know how. I wouldn't mind if the averages were repeated among the related records, I could deal with that with another layout and a conditional field.

(Filemaker Pro 11 Macintosh)

Any suggestions?

Thank you,

Bruce

[comment]

I am somewhat confused by your example. Is the average of "Image 1" (25) supposed to consider only images numbered 1 in the 0 Glu group? Or is the Image 1 in the 3 Glu group supposed to be included?

[bac mac]

Yes to your first question, No to your second- Image=1 values in other groups will have their own averages but not be included in the analysis of Group 0 Glu.

Each Group (0 Glu, 3 Glu, etc.) is subgrouped by the Image field (1, 2, etc.). There will be unknown members per Group, and unknown numbers of subgroups, although the subgrouping will be generally repetitive and usually be the same for each Group. So I do need an average of values for all records where Group is the same and I need averages for subgroup where Group is the same and Image is the same.

If there was a way to generate a list of the values from the desired set of records, it seems that I would be able to average that list. But I'm not sure if that could be implemented or how it would be done, or if there is a better way.

[comment]

This should be very easy, then. Use a list layout with two sub-summary parts: one for Group, and one for Image. Place the summary field in both parts. Sort your records by Group and by Image.

[bac mac]

I actually tried that first. I'm sure you're right, but for some reason, I couldn't get the additional sub-summary part to break correctly. Even when I told it to break on the subgroup field, it just appears immediately above the group summary and displays the same value. I just tried it on a new simple file and it worked fine, so I'm sure that you're right that the sub-summary parts are the way to go.

But, is it possible to generate a list of the values in a field across selected records and, if so, would it be possible to average that list?

Thanks, Bruce

[comment]

But, is it possible to generate a list of the values in a field across selected records and, if so, would it be possible to average that list?

I am not sure what exactly you mean by that. A summary field defined as Average of [Value] will average the values across the found set. If you place the same field in a sub-summary part, it will display the average of the sorted group (subset).

[bac mac]

I thought I might have some additional options with a List function. But actually, I do now have the dual sub-summaries working fine. Hearing that that was the way to go from you brought me back to it and I actually had a trivial glitch preventing me from getting that to work earlier- a field calculation that was set to output as a number instead of text messing up the break field.

Is there anything that I should do to a post to indicate that it was solved?

Thanks very much!

Bruce