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!

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)