Charting Found Set including Total Records Average
I have a very specific question that I haven't been able to find the answer for. If someone could point me in the right direction to achieve this that would be great. It's relating to charting a found set, with the inclusion on that chart of the average value of a field that takes into account more records than just the found set.
To outline what I have to try to make it easier to understand, I have numerous vessels. On each vessel I have numerous equipments such as Main Engine Port, Gearbox etc. Data for each equipment includes the Make and Model of each. Some vessels will have the same Make and Model for equipment like, Volvo 9000 for a Main Engine Port. I also have Oil Sample data associated with each equipment like Iron content of the oil (as one example, there are many more).
At the moment, I am charting the data of Iron (and other contents) over a period of time from oil samples collected on a regular basis. I am fine up to this point, everything is working great to acheive this. What I would like to do though, is if this vessel has a Volvo 9000 Main Engine Port, I would like to compare the Iron content for this main engine port to an average value that is calculated from ALL the Main Engine Ports over all the vessels that have a Make and Model that is Volvo 9000. So basically, what I am asking is how do I get an average value of all the records of a piece of equipment with a specific make and model so I can compare that to my current found set for that same make and model.
I have attached a screenshot of the relationships. The info relating to the Equipment is in the Equipment_List table and all the oil sample data for charting is in the Oil Sample Data | Display Trends TO. Hope this explanation is kind of clear enough. Many thanks in advance.