locked
Aggregate function acoss a column value RRS feed

  • 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

Answers

  • 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)

    Please see below screenshots.

    Let me know if you have any questions.

    Please Mark this reply as answer if it has solved your issue. Or please use Vote As Helpful if my post was useful so that other forum members can benefit from it.

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

    Thank you for your reply. 

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

    If you have any questions, please feel free to ask. 

    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)

    Please see below screenshots.

    Let me know if you have any questions.

    Please Mark this reply as answer if it has solved your issue. Or please use Vote As Helpful if my post was useful so that other forum members can benefit from it.

    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. 

    If you have any question, please feel free to ask. 

    Best Regards,
    Henry 


    • Edited by Henry Jiang Friday, April 21, 2017 8:21 AM
    • Proposed as answer by Henry Jiang 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..

    Please suggest.

    Cheers

    Jim

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

    Thank you for your reply. 

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

    If you have any questions, please feel free to ask. 

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