Add an overall average row to Report Builder report

Question

I need help creating an overall average in Report Builder 3.0. I have a query that returns data in the following format:

Major       Num Of Students     Max GPA     Min GPA     Avg GPA
----------  ------------------  ----------  ----------  -----------
Accounting  89                  4.0         2.3         3.68
Business    107                 4.0         2.13        3.23
CIS         85                  3.98        2.53        3.75

I added a total row in Report Builder that shows the sum number of students, overall Max GPA, and overall Min GPA. But I can't simply run the Avg function on the Avg GPA column, as it needs to take into account the number of students for an overall average. I believe that I need to do something like the following (in pseudocode):

foreach ( row in rows ) {
    totalGpa      += row.numOfStudents * row.avgGpa
    totalStudents += row.numOfStudents
}

overallAvgGpa = totalGpa / totalStudents

Does anyone know how I could do this in my report?

Ian Preston Ian Preston · Accepted Answer · 2014-03-10T15:30:03

In your case you need weighted average here, something like this in the Total row:

=Sum(Fields!numOfStudents.Value * Fields!avgGpa.Value)
  / Sum(Fields!numOfStudents.Value)

You can see I'm creating the expression Fields!numOfStudents.Value * Fields!avgGpa.Value for each row, summing that, then dividing by the total students.

In your case this would give (89 * 3.68 + 107 * 3.23 + 85 * 3.75) / (89 + 107 + 85), i.e. 3.53, which seems about correct.

Add an overall average row to Report Builder report

1 Answers