Weighted Average or SUMPRODUCT in SSRS 2008

• derrysql

  Old Hand

  Points: 308

  More actions

  March 12, 2012 at 6:16 am

  #269818

  Hi all,

  SSRS 2008 R2

  I am converting some spreadsheets to reporting services but I am having a specific problem in calculating a weighted average for some values. The spreadsheet users use the SUMPRODUCT function in Excel but there does not seem to be an equivalent in SSRS.

  Imagine the following dataset

  Location Department Calls Average Talktime

  New York New Sales 10 200

  New York Admin 20 150

  In the report I am grouping by Location and for the New York Location the following needs to be displayed.

  Location Calls AverageTalkTime

  New York 30 166.66

  The 166.66 value is worked out in Excel using sumproduct and it is also a weighted average. The equivalent manual formula without sumproduct is SUM((D4*E4)+(D5*E5))/SUM(D4:D5) which is SUM((10*200)+(20*150))/SUM(10:20)

  In this formula the calls are in column D and the talk time in column E.

  We cannot work out how to do this in SSRS at all, any ideas?

  Thanks in advance,

  ds

• Daniel Bowlin

  SSC-Dedicated

  Points: 34566

  More actions

  March 12, 2012 at 7:19 am

  #1457832

  I think if you add a column to your dataset of Calls * AvgTalkTime you should be able to work out the rest of the formula in SSRS.