calculate average at detail level on report

  • Thread starter Thread starter DJ
  • Start date Start date
D

DJ

Although I've created a calculated average control and placed it within the
detail section of the report, it appears to be returning a "grand average" of
all averages on the report. My line reads:

=Avg([Scores]![Jan]+[Scores]![Feb]+[Scores]![Mar])

I would expect this if I had the control in the foooter, but it's not. How
can I get an average of the Jan, Feb and Mar scores at each line item
(detail) level?
 
Except that in some cases, a score might be missing. So I am truly looking
for an average (of available scores) and not a sum/3.

KARL DEWEY said:
=([Scores]![Jan]+[Scores]![Feb]+[Scores]![Mar])/3


DJ said:
Although I've created a calculated average control and placed it within the
detail section of the report, it appears to be returning a "grand average" of
all averages on the report. My line reads:

=Avg([Scores]![Jan]+[Scores]![Feb]+[Scores]![Mar])

I would expect this if I had the control in the foooter, but it's not. How
can I get an average of the Jan, Feb and Mar scores at each line item
(detail) level?
Click to expand...
Click to expand...
 
=(IIF([Scores]![Jan] Is Null, 0, [Scores]![Jan])+IIF([Scores]![Feb] Is Null,
0, [Scores]![Feb])+IIF([Scores]![Mar] Is Null, 0, [Scores]![Mar])) /
(IIF(=([Scores]![Jan] Is Null, 0, 1)+IIF([Scores]![Feb] Is Null, 0,
1)+IIF([Scores]![Mar] Is Null, 0, 1))


DJ said:
Except that in some cases, a score might be missing. So I am truly looking
for an average (of available scores) and not a sum/3.

KARL DEWEY said:
=([Scores]![Jan]+[Scores]![Feb]+[Scores]![Mar])/3


DJ said:
Although I've created a calculated average control and placed it within the
detail section of the report, it appears to be returning a "grand average" of
all averages on the report. My line reads:

=Avg([Scores]![Jan]+[Scores]![Feb]+[Scores]![Mar])

I would expect this if I had the control in the foooter, but it's not. How
can I get an average of the Jan, Feb and Mar scores at each line item
(detail) level?
Click to expand...
Click to expand...
Click to expand...
 
This won't yield the correct answer (average of available scores). For
example:

Avg 92 + Null + 96 = 94

Avg 92 + 0 + 96/3 = 62.66

KARL DEWEY said:
=(IIF([Scores]![Jan] Is Null, 0, [Scores]![Jan])+IIF([Scores]![Feb] Is Null,
0, [Scores]![Feb])+IIF([Scores]![Mar] Is Null, 0, [Scores]![Mar])) /
(IIF(=([Scores]![Jan] Is Null, 0, 1)+IIF([Scores]![Feb] Is Null, 0,
1)+IIF([Scores]![Mar] Is Null, 0, 1))


DJ said:
Except that in some cases, a score might be missing. So I am truly looking
for an average (of available scores) and not a sum/3.

KARL DEWEY said:
=([Scores]![Jan]+[Scores]![Feb]+[Scores]![Mar])/3


:

Although I've created a calculated average control and placed it within the
detail section of the report, it appears to be returning a "grand average" of
all averages on the report. My line reads:

=Avg([Scores]![Jan]+[Scores]![Feb]+[Scores]![Mar])

I would expect this if I had the control in the foooter, but it's not. How
can I get an average of the Jan, Feb and Mar scores at each line item
(detail) level?
Click to expand...
Click to expand...
Click to expand...
 
I do not see how you came to the conclusion that 'Avg 92 + Null + 96 = 94'
and 'Avg 92 + 0 + 96/3 = 62.66'.

The formula I posted first tested each field for null, and if was, used 0
(zero) instead of the field. It added them.
The second part counts how many fields are not null to determine the divisor.
By dividing the sum by count of fields with data you get the average.


DJ said:
This won't yield the correct answer (average of available scores). For
example:

Avg 92 + Null + 96 = 94

Avg 92 + 0 + 96/3 = 62.66

KARL DEWEY said:
=(IIF([Scores]![Jan] Is Null, 0, [Scores]![Jan])+IIF([Scores]![Feb] Is Null,
0, [Scores]![Feb])+IIF([Scores]![Mar] Is Null, 0, [Scores]![Mar])) /
(IIF(=([Scores]![Jan] Is Null, 0, 1)+IIF([Scores]![Feb] Is Null, 0,
1)+IIF([Scores]![Mar] Is Null, 0, 1))


DJ said:
Except that in some cases, a score might be missing. So I am truly looking
for an average (of available scores) and not a sum/3.

:

=([Scores]![Jan]+[Scores]![Feb]+[Scores]![Mar])/3


:

Although I've created a calculated average control and placed it within the
detail section of the report, it appears to be returning a "grand average" of
all averages on the report. My line reads:

=Avg([Scores]![Jan]+[Scores]![Feb]+[Scores]![Mar])

I would expect this if I had the control in the foooter, but it's not. How
can I get an average of the Jan, Feb and Mar scores at each line item
(detail) level?
Click to expand...
Click to expand...
Click to expand...
 
Got it now. I was moving too fast. Thanks for the help!

KARL DEWEY said:
I do not see how you came to the conclusion that 'Avg 92 + Null + 96 = 94'
and 'Avg 92 + 0 + 96/3 = 62.66'.

The formula I posted first tested each field for null, and if was, used 0
(zero) instead of the field. It added them.
The second part counts how many fields are not null to determine the divisor.
By dividing the sum by count of fields with data you get the average.


DJ said:
This won't yield the correct answer (average of available scores). For
example:

Avg 92 + Null + 96 = 94

Avg 92 + 0 + 96/3 = 62.66

KARL DEWEY said:
=(IIF([Scores]![Jan] Is Null, 0, [Scores]![Jan])+IIF([Scores]![Feb] Is Null,
0, [Scores]![Feb])+IIF([Scores]![Mar] Is Null, 0, [Scores]![Mar])) /
(IIF(=([Scores]![Jan] Is Null, 0, 1)+IIF([Scores]![Feb] Is Null, 0,
1)+IIF([Scores]![Mar] Is Null, 0, 1))


:

Except that in some cases, a score might be missing. So I am truly looking
for an average (of available scores) and not a sum/3.

:

=([Scores]![Jan]+[Scores]![Feb]+[Scores]![Mar])/3


:

Although I've created a calculated average control and placed it within the
detail section of the report, it appears to be returning a "grand average" of
all averages on the report. My line reads:

=Avg([Scores]![Jan]+[Scores]![Feb]+[Scores]![Mar])

I would expect this if I had the control in the foooter, but it's not. How
can I get an average of the Jan, Feb and Mar scores at each line item
(detail) level?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top