Hey Josh - The short answer, in my opinion, is to summarize the data into another table and then pass the average value back down to the "Properties" table.
The long answer:
You are wanting more than just the current record summarized, so you add a parent table that summarizes the [Build Cost] and [SqFt]. That summary table will have two fields that contain the summary of the "Properties" table. Then create your average calculation field on that table.
Lastly, take the average calculation field and pass it back down to the "Properties" table. It may feel like a lot of effort for a simple number, but it also gives you some good flexibility. For example, you may want to only summarize records for the last 2 months, or ones that are open, etc...
Cheers,
John Harvey, MBA CBIP QBCE
Regional Director
Business Intelligence Reporting Services
www.GetReportHelp.com
John@getreporthelp.com
(Office) 208 572-0585
(Cell) 406-334-9882
Schedule a Meeting
