How to get the average value of column in SSRS

Question

I have a table like this.

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

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.

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....

Can anyone help me? Thanks!

Damato

What version of SSRS is this? Is it 2008R2 or higher? – Access

Answer 1

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)

Kaylyn

Thanks, but your solution will not work in one situation that when the row total value is nothing of a commodity, for example if Road Bikes row total value is nothing, then the average value should be (3 + 2) / 2 not (3 + 2) / 3, I need to exclude empty value in total column. – Damato

Answer 2

This should work:

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

Recuperate