I don't think you can do it the way you described, based on your example. You have Customer ID 10 in the header of your 2nd example, yet you want an average for products that span more then one Customer ID (in this case Customer ID 20), more then one product id, and summarize by description.
Execute SQL may be the only way to do this.
ExecuteSQL is an option but a self join that matches by ProductID might also serve.
If you create another occurrence of this table in the relationship graph and link it to your layout's table occurrence by Product ID then you can use either the Average function or the Average summary field to compute that average. If you use a summary field, it must be added to your layout from the new, related table occurrence. If using the Average function it must be in a calculation field that evaluates from the context of your layout's table occurrence with this expression: Average ( NewTableOccurrenceName::Cost ).
The self join worked perfectly, thanks Phil - that's a massive step, believe me.
Here's the curveball - how to arrive at the upper and lower quartile figures for the same set of data.
Any ideas on how that might work?
I don't recall off hand the math needed to produce such results. I seem to recall that standard deviation plays a role in this? Or am I way off base? (Standard Deviation is another aggregate value that a summary field or aggregate function can calculate--so that might be part of what you need to do this.)
I'm not 100% myself (is that a stat pun?), I've been asked to put it together as further columns, so need to find out first how to get there on paper...
I think you divide a list of numbers ranked high to low, by 4. Top quartile is the last number in the top section?? I'm not certain.