Summary rows need to include overall averages of each row item
I have a data set which follows this simple example below:
Customer ID ProductID Description Cost
10 1 Thing 5.00
10 2 Doodah 7.50
10 3 Whatsit 15.00
20 1 Thing 6.50
20 2 Doodah 9.00
20 3 Whatsit 17.00
Each customer sells the same items for a slightly different price. What I need to do in a list layout summary report is on a row by row basis view a customer's price for an item alongside the average price for that item for ALL customers, like this:
Customer ID 10
ProductID Description Customer Price All Average Price
1 Thing 5.00 5.75 (5+6.5/2)
2 Doodah 7.50 8.25 (7.50+9/2)
3 Whatsit 15.00 16.00 (15+16/2)
I'm struggling to get the all average price per product to appear on the same product's row per customer report. I've tried to use a GetSummary(Price;ProductID) and then on a second field, a Summary(Average) of that, but it's not worked.
I have around 200 products to calculate this for so I don't really want to use a ProductID specific calculation (such as you might use in a SQL where clause).
So in summary (no pun intended), I need an average of the cost of all customer's ProductID1's to appear on ProductID1's row and so on.
Thanks in advance