Calculating Weighted Average in a Tablix in SSRS 2008 R2

2
votes

I have a Tablix in SSRS 2008 R2. The columns are departments. The rows are different stats for the departments.

One of the rows has to display the average speed of answer for incoming calls. I have that filed in my data set: AvgSoA.

I also have a field for the total number of answered calls: NumOfCalls.

The last column of the Tablix summarizes all the departments.

What I don't know is how to calculate the weighted average speed to answer.

Of course I know what a weighted average is... I just don't know how to calculate it in the last column of the Tablix since I don't know how many departments will "show up" in the report.

Thanks.

2
reporting-servicesssrs-2008
Are you able to add some simple example data and your expected results? – Ian Preston

2 Answers

4
votes

It actually doesn't matter how many departments show up because the departments aren't involved in the weighted average calculation. What you need is a value for a unit, in your case AvgSoA, and the number of occurrences of that value, in your case NumOfCalls. For the weighted average, you sum the products of these, then divide by the sum of the occurrences.

So in your dataset create a field for the NumOfCalls times the AvgSoA - this is your product, let's call it Weight. Now we have our products per row, so in the total row we simply need to sum Weight and divide by the sum of the NumOfCalls to get the weighted average:

=SUM(Fields!Weight.Value) / SUM(Fields!NumOfCalls.Value)
0
votes

I had this same issue as Sum does handle null values in your data set properly. To correct for this I found it best to do null checks on all your input values. If I use your example:

Sum((IIF(IsNothing(Fields!NumOfCalls.Value),0,Fields!NumOfCalls.Value)/Sum(IIF(IsNothing(Fields!NumOfCalls.Value),0,Fields!NumOfCalls.Value)))*IIF(IsNothing(Fields!AvgSoA.Value),0,Fields!AvgSoA.Value))

I know this looks cumbersome, but if you remove the null checking all it is:

Sum((Fields!NumOfCalls.Value/Sum(Fields!NumOfCalls.Value))*Fields!AvgSoA.Value)

I would not recommend removing the null checking though as any null value in the data will through off your answer.