AVG returning 1 as the result rather than true average

• derrysql

  Old Hand

  Points: 308

  More actions

  September 4, 2012 at 5:32 am

  #281067

  Hi all,

  SQL 2008 report builder.

  I have a strange problem with the displaying of averages using the AVG function,

  I have a tablix which has row groups of country and state.

  In my data set I also have a field called countme and this is set to 1.

  Within each grouping I am summing countme so I use SUM(fields!countme) at each grouping level and this works correctly.

  So imagine on screen the report is

  Country State Total

  USA 365

  NY 75

  SC 75

  CA 75

  FL 75

  LA 65

  The issue is that instead of having a total at the country level we wish to have an average, so at the country grouping level I amend the column to show AVG(fields!countme)

  Using the example above, rather than showing an average of 73 the formula returns 1 (or 1.00 if I set the text box to 2 decimal places)

  Any suggestions on this? I have tried various solutions but to no avail including reference the grouping scope eg AVG(fields!countme, "country")

  Any ideas?

  Thanks in advance,

  osd

• Charles Hearn

  SSCrazy

  Points: 2680

  More actions

  September 4, 2012 at 5:50 am

  #1533219

  Divide your sum by the CountRows() function