Create Statistic fields for Total and Average.
Then create a new List layout and add a Part for Customer.
Put Customer name and Total and Average on that Part.
Delete the Body Part
Sort your records on Customer and what you will see is summarised Total and Average for each Customer
If you like to add total for all Customers, add a Trailing Grand summary and add all those fields again
Thanks for this - what I need per row would be something like:
Customer Price Average of all other customers prices
Product X 500.00 550.25
Product Y 250.00 298.10
This compares the actual price of product X for customer A against the overall average price for product X for all customers A-Z (where customers B-Z are not already in the found set).
Then you just add a second Sub Summary Part for Product.
In your sort you add after Customer, Product
This will create following nice summarised report
Customer - Total and Average
Product per Customer - Total and Average
The reporting for me is fine with the parts and sorting etc. I need to get the overall average of 20,000 and show it on this report on a per row basis, for example:
Records 1 - 3 in found set might be as such:
ID Detail Value National Average
1 Product X 500.00 500.00 <<< Incorrect as it does not include values from 2 and 3 outside of found set.
2 Product X 575.37 <<< Not shown on report as separate row from another customer, used only for calc
3 Product X 575.38 <<< Not shown on report as separate row from another customer, used only for calc
In the example above, I need the national average to actually show 550.25. However, because records 2 and 3 are outside of the found set (by virtue of the initial script I run), they will not be included in the summary average calc (hence the use of SQL to find and include them).
Hope thats somewhat clearer.
I suggest using a script to find those records for the national average, compute the needed statistical values via your summary fields and then store them into a different record in indexed number fields. You can then include them via relationship in your regular report.
You could just use a self join relationship and reference your summary fields, but this can be very slow if you have lots of records. If you pre-calculate and store the values, you only need calculate the values once until the next time you need to update them.