Any suggestion on how to get the correct average? [Avg(WorkPlanPct)]

Hi Craig, You could use following function: Public Function DIV(ByVal Op1 as Double, ByVal Op2 as Double) as Double IF (IsNothing(Op2) OR Abs(Op2) < 0.0000001) Then Return Nothing ELSE Return (Op1/Op2) END IF End Function Here is an example: Code.DIV(SUM(Fields!IAmount.Value), SUM(Fields!Quantity.Value)) Remember to mark as an answer if this post has helped you.

Looking at what you provided for the expression: "Code.DIV(SUM(Fields!IAmount.Value), SUM(Fields!Quantity.Value))" This is what have but does not work. Can you recommend how to correct. I did not fully understand you answer? =avg(Fields!WorkPlanPct.Value)

In your particular case it would be: Code.DIV(SUM(Fields!WorkPlanPct.Value), COUNT(Fields!WorkPlanPct.Value)) Remember to mark as an answer if this post has helped you.

almost there, i get a syntax error on the comma between Value) and COUNT (Fields) =(Sum(Fields!WorkPlanPct.Value),(Count(Fields!WorkPlanPct.value))

Opening bracket before Count isn't needed. Using DIV function allows to avoid division by zero. Remember to mark as an answer if this post has helped you.

I appreciate all the help, but it will not work still. Stumped

What part doesn't work? Did you add DIV function in the Code section ofr your report?Remember to mark as an answer if this post has helped you.

Hi Craig, Suppose there are three rows in WorkPlanPct: 1, null, 2. What’s the expected Average value? It's 1.5, 1, or something else? By default, the AVG function will ignores NULL values automatically, so the value of AVG (Fields!WorkPlanPct.Value) should be (1 + 2) / 2 = 1.5. If you don't want to ignore NULL values and change the behavior of AVG function to (1 + 2) / 3 = 1, then you can use the expression: =AVG(IIF(Fields!Num.Value IS NOTHING, 0.0, CDBL(Fields!Num.Value))) Thanks, Albert Ye

Igor, the expression provided failed to return any value. The other expressions tried including Alberts returned values but those values were not the averages. I have tried modifying the expression on each suggestion and still not getting the average. The quick representation below shows expected results and how it should appear in SSRS. Each expression below shows what was tried and what happened. Year 1 Type Name Month 1 Month 2 Month 3 Sum Expected Average Result Perm Jane 6 4 5 15 5.0 John 5 3 4 12 4.0 Harry 5 5 6 16 5.3 Perm Total 16 12 15 43.0 14.3 Returned expected result =SUM(Fields!WorkPlan_.Value) returned unexpected result =AVG(Fields!WorkPlanPct.Value) Failed to return anything Code.DIV(SUM(Fields!WorkPlanPct.Value), COUNT(Fields!WorkPlanPct.Value)) returned unexpected result =AVG(IIF(Fields!Num.Value IS NOTHING, 0, CDBL(Fields!Num.Value)))

I resolved this in the query itself rather than making use of the AVE expression in the report. Cheating a bit, but have reached the point that the former approach had to be abandoned.

Hi Craig, In fact, avg function works well in your scenario. Actually, avg is for the rows in dataset, not the cells in report. Suppose the sum value is 10, there are 9 rows in dataset, and 3 cells with aggregate value in report, the avg function should be 10/9, not 10/3. That's why avg function cannot get your expect value in your report. To achieve your purpose, my suggestion is that you can use countdistinct function, such as: =Sum(Fields!Salary.Value)/CountDistinct(Fields!Month.Value) Thanks, Albert Ye