Weighted average in SSRS 2008 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

Weighted average in SSRS 2008

Author

Topic

opensourcederry
Starting Member

12 Posts

Posted - 2012-03-12 : 08:22:22

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 a manual formula 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 a formula and it is also a weighted average. The equivalent manual formula 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,

osd

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-12 : 10:16:24

isnt it this?

=SUM(Fields!Calls.value * Fields!AverageTalktime.value)/SUM(Fields!Calls.value)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources