I need a new column which is an average price of the three columns before it - the average needs to stay in the scope of the item...but when I try it calculates the average of the sum - not the item level avg.. Price 1 Price 2 Price 3 AVG Item1 1.00 2.00 3.00 2.00 Item 2 3. 00 3.00 6.00 4.00 I've Tried this =AVG((PRC1) +(PRC2)+(PRC3)) - but it gives me like the average of the grand total of all the projects...KDW

I think you need to add GROUP BY Item.. I woud prefer doing such things in T-SQLBest Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

I would rather do it in the rdl - the sql is using lots of calculations to arrive at the priceKDW

Perhaps I'm missing something here: Why not: Average_Price= (Price1 + Price2 + Price3)/3 --DanDJAnsc

Because if one of them is null I don't want it dividing two prices by 3....KDW

Hi, I guess you are using Matrix to pivot your data. Rights? If so, for SSRS 2005, add the subtotal column, use =IIF(Inscope("PriceColumnGroup"),SUM(Fields!Amount.value),AVG(Fields!Amount.value)) to achieve this. for SSRS 2008, modify the total column cell expression like this: =AVG(Fields!Amount.value) thanks, Jerry

Hi, I guess you are using Matrix to pivot your data. Rights? If so, for SSRS 2005, add the subtotal column, use =IIF(Inscope("PriceColumnGroup"),SUM(Fields!Amount.value),AVG(Fields!Amount.value)) to achieve this. for SSRS 2008, modify the total column cell expression like this: =AVG(Fields!Amount.value) thanks, Jerry

No - I pivoted in sql using table aliases so i want to avg using expressions in the table I found it - =(IIF(IsNothing(fields!os_main.Value),0,(fields!os_main.Value)) + IIF(IsNothing((fields!anda_prc.Value)),0,(fields!anda_prc.Value)) + IIF(IsNothing((fields!abc_cmpt_prc.Value)),0,(fields!abc_cmpt_prc.Value))+ IIF(IsNothing((fields!card_prc.Value)),0,(fields!card_prc.Value))) / (IIF(IsNothing((fields!os_main.Value)),0,1) + IIF(IsNothing((fields!anda_prc.Value)),0,1) + IIF(IsNothing((fields!abc_cmpt_prc.Value)),0,1)+ IIF(IsNothing((fields!card_prc.Value)),0,1)) KDW