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

# Summary rows need to include overall averages of each row item

### 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.

• ###### 1. Re: Summary rows need to include overall averages of each row item

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.

• ###### 2. Re: Summary rows need to include overall averages of each row item

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 ).

• ###### 3. Re: Summary rows need to include overall averages of each row item

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?

Thanks

• ###### 4. Re: Summary rows need to include overall averages of each row item

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.)

• ###### 5. Re: Summary rows need to include overall averages of each row item

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.