How to get the average value of column in SSRS

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!

Answers


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)

Need Your Help

How index cifs server with solr

solr cifs

is it possible to index some document on cifs server with solr ?

menustrip and custom message box

c# winforms

I have created a custom login form. When a user clicks on a menustrip tool, I show the login form. If he enters the correct username and password, I want this form to be closed and disabled and the...