calculate average at detail level on report

[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?

 

[KARL DEWEY]

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

 

[DJ]

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?

 

[KARL DEWEY]

=(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?

 

[DJ]

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

=(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?

 

[KARL DEWEY]

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.

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

=(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?

 

[DJ]

Got it now. I was moving too fast. Thanks for the help!

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.

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

=(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?