5 Replies Latest reply on Aug 13, 2015 8:34 AM by Stu412

    Summary rows need to include overall averages of each row item

    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