# Aggregate function acoss a column value

• ### Question

• Hi All,

I have matrix, I want to take Average of Peak for East, Avg of Peak for West, Avg of Peak for North so on and so forth.. I'm using avg() but not it is not calculating avg per region wise. It is taking Average for all regions and giving same value for EAST, West, etc..

As per my understanding, if I apply - Avg(Fields!Column.Value, "Dataset Name") in the measure text box of matrix, it should automatically calculate the w.r.to particular region. But it is not happening. Please suggest.

East   West  North South

Peak           105   105    105   105

Non-Peak       112   112    112  112

All time       107   107    107   107

Cheers
Jim

Thursday, April 20, 2017 2:55 PM

• Hello Jim,

You need to use Below expressions in your report to calculate column averages or row averages.

=Sum(Fields!number.Value) /CountDistinct(Fields!TimeFrame.Value)

=Sum(Fields!number.Value)/Countdistinct(Fields!Direction.Value)

Let me know if you have any questions.

Thursday, April 20, 2017 6:27 PM
• Hi Jim,

I think it is because when we use aggregate functions such as AVG(), SUM(), by default, if we don’t specify a scope, it will do the aggregate function within the dataset. However, if the calculated field is under a group (row group or column group) and we don’t specify a scope, it will do the aggregate function with the group.

Since you are using a matrix (with two groups, one is row group and the other is column group), and the requirement is exactly calculating the average value corresponding to row and column, by default, the AVG() function will take effect with the row scope and column scope (which is expected). However, if we add the “DataSet” Scope, the aggregate function will not take effect in the right scope. (DataSet scope is different from row plus column scope).

Best Regards,
Henry
Friday, April 21, 2017 9:57 AM

### All replies

• Hello Jim,

You need to use Below expressions in your report to calculate column averages or row averages.

=Sum(Fields!number.Value) /CountDistinct(Fields!TimeFrame.Value)

=Sum(Fields!number.Value)/Countdistinct(Fields!Direction.Value)

Let me know if you have any questions.

Thursday, April 20, 2017 6:27 PM
• Hi Jim,

Based on your scenario, we only need to use the expression

`=AVG(Fields!Column.Value) `

Below is my testing results:

In the first screenshot, the table on the left is the data I used (including time, direction and calculated fields). The matrix on the right is the average results for different directions and for different time periods. In the matrix, there is a row group “time” and a column group “directions”.

The second screenshot is my preview results. So each direction and each time period correspond to an average value.

May I know whether this is the result you want?

If yes, the issue lies in that you are using a matrix to render the value, so we need to calculate the average value under the scope of time group (peak, non-peak, all time)as well as the scope of direction group (east, west, north, south), not the scope of DataSet.

If this is not the result you want, may I know how is your report design like so that I can better understand the issue. Thank you.

Best Regards,
Henry

• Edited by Friday, April 21, 2017 8:21 AM
• Proposed as answer by Friday, April 21, 2017 9:56 AM
Friday, April 21, 2017 8:18 AM
• Hi,

Thanks for answering.. The same Avg() worked for me. Only change that I did is,

Earlier - Avg(Fields!number.Value , "Dataset name")

Now -    Avg(Fields!number.Value)

It is working fine.. But wondering why removing the dataset name gave the correct avg for the measure across corresponding row and column..

Cheers

Jim

Friday, April 21, 2017 9:08 AM
• Hi Jim,