Search code examples
reporting-servicesssrs-2008ssrs-2012

Creating an average expression

I have the following matrix consisting of Fiscal,Month and ID fields. The values are a count of the ID.

Fiscal        Apr    May   March        
2010/11        5      5      2
2011/12        3      2      3
2012/13        2      6      1           
Total         10     13      6

I would like to have an average row added but not sure how to do it. Have looked at the AVG function but not sure how to work it with a count.

Fiscal        Apr    May   March
2010/11        5      5      2
2011/12        3      2      3
2012/13        2      6      1
Total         10     13      6
Average        3      4      1

This Is how the matrix looks in design view

   Fiscal       Month                    Total
( [Fiscal]      Count(Fields!ID.Value)   Count(Fields!ID.Value)
   Total        Count(Fields!ID.Value)   Count(Fields!ID.Value)
Solution

  • The average is the sum divided by the number of data points. Therefore you should be able to use the total count divided by the number of groups. To get the number of groups you can use `CountDistinct" to count distinct values of Fiscal (since this is what your dataset is grouped by):

    =Count(Fields!ID.Value) / CountDistinct(Fields!Fiscal.Value)