How to get the average value of column in SSRS

5
votes

I have a table like this.

Data Table

And I want to use SSRS to present the report like this.

Report

I use a Matrix in this report. Add [Category], [Commodity] to row group, add [SaleDate] to column group, add Sum(SaleAmount) to column data, add Sum(SaleAmount) for [Commodity] row group, add Avg(SaleAmount) in the last cell.

enter image description here

But the value of Avg(SaleAmount) is not like (3+5+2)/3 = 3.3333, its value is （2+1+3+2+1+1）/6 = 1.666666....

enter image description here

Can anyone help me? Thanks!

2
reporting-servicesmatrix
What version of SSRS is this? Is it 2008R2 or higher? – Ian Preston

2 Answers

9
votes

The average is just the sum divided by the count. In this case the sum is the same, but you want the count to just be the different commodities. Using CountDistinct should work:

=SUM(Fields!SaleAmount.Value) / COUNTDISTINCT(Fields!Commodity.Value)
0
votes

This should work:

=IIF(COUNTDISTINCT(Fields!Commodity.Value) > 0 ,SUM(Fields!SaleAmount.Value) / COUNTDISTINCT(Fields!Commodity.Value),Nothing )