Hi All, Is there a way to average a group of values based on a condition? Below is my example. Data Set: ITEM QTY Canceled Apples 3 0 (Bool Type) Apples 8 1 Apples 5 0 Report Structure & Expected Results Example ITEM Average Canceled Apples 4 1 (3+5)/2 (3+8+5)/3 What I have to avoid is including the QTY value if a canceled condition exists in the canceled column, in my case because the 8 has been canceled I don’t want this included in my average result. Flexpadawan

Hi, One easy way is to do grouping by Cancelled and then perform average. Step-1:- Dataset declare @t table ( ITEM nvarchar(100), QTY int, Cancelled bit ) insert into @t select 'Apples',3,0 union all select 'Apples',8,1 union all select 'Apples',5,0 select * from @t Step-2:- Create group on "ITEM" and then on "Cancelled" Step-3:- Set following visibility expression on group: =IIF(Fields!Cancelled.Value , TRUE, FALSE) -Chintak (My Blog)

Hi, One easy way is to do grouping by Cancelled and then perform average. Step-1:- Dataset declare @t table ( ITEM nvarchar(100), QTY int, Cancelled bit ) insert into @t select 'Apples',3,0 union all select 'Apples',8,1 union all select 'Apples',5,0 select * from @t Step-2:- Create group on "ITEM" and then on "Cancelled" Step-3:- Set following visibility expression on group: =IIF(Fields!Cancelled.Value , TRUE, FALSE) -Chintak (My Blog)

Your solution worked perfectly, thanks! The only thing I didn't do was group by canceled .Flexpadawan

I might have replied too soon. The solution works on small data sets but when applied to larger ones what ends up happening is the Canceled field goes blank on some items. I took the same data set and ran it through Excel pivot table and it returned all the correct values, no blanks. Report Structure & Expected Results Example ITEM Average Canceled Apples 4 1 Pears 8 <blank on report> <--Underlying data is 1 Grapes 6 1 Bananas 12 0 ..... Flexpadawan

I might have replied too soon. The solution works on small data sets but when applied to larger ones what ends up happening is the Canceled field goes blank on some items. I took the same data set and ran it through Excel pivot table and it returned all the correct values, no blanks. Report Structure & Expected Results Example ITEM Average Canceled Apples 4 1 Pears 8 <blank on report> <--Underlying data is 1 Grapes 6 1 Bananas 12 0 ..... Flexpadawan

This is the fix but I don't know why it worked because my missing fields had values??? Anyway my report works now! Replace =IIF(Fields!Canceled.Value, TRUE, FALSE) With =IIF(IsNothing(Fields!Canceled.Value), TRUE, FALSE)Flexpadawan

This is the fix but I don't know why it worked because my missing fields had values??? Anyway my report works now! Replace =IIF(Fields!Canceled.Value, TRUE, FALSE) With =IIF(IsNothing(Fields!Canceled.Value), TRUE, FALSE)Flexpadawan

Hi, It looks like, you are getting NULL from database for field Canceled.-Chintak (My Blog)