AnsweredAssumed Answered

Summary rows need to include overall averages of each row item

Question asked by Stu412 on Aug 13, 2015
Latest reply on Aug 13, 2015 by Stu412

Title

Summary rows need to include overall averages of each row item

Post

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

 

Outcomes