# Sum and Avg in SSRS

I have many tables that I need to sums and avg of.
Here is the table that has a calculated field.

=ROUND(Fields!JHVolume.Value)*(Round(Fields!BartleyEngery.Value/Fields!BartleyVolume.Value,4))

This works and give the value I need

Now I need to do a sum and a avg. From this table.

Here is what I have tried.
=sum(Fields!JHVolume.Value, "DataSetTotal")*sum(Fields!BartleyEngery.Value/Fields!BartleyVolume.Value,"DataSetTotal")

It gives me a crazy total.
Any help?

Sam
###### Who is Participating?

Commented:
Looking at that expression, I don't see any reason that the sum would be doubled.  Are you sure that the data underneath the expression isn't doubled?  The average would still look the same, but the sum would be doubled.

Greg

0

Commented:
You're multiplying the two sum together.  Are you sure that's what you want to do?  Also, you can use the AVG function to get the average.

Greg

0

Author Commented:
Jester, yes here is the expression I used.

=sum(ROUND(Fields!JHVolume.Value, "DataSetTotal")*(Round(Fields!BartleyEngery.Value/Fields!BartleyVolume.Value,4, "DataSetTotal")))

and this is the error message.

[rsMissingAggregateScope] The Value expression for the textbox textbox28 uses an aggregate expression without a scope.  A scope is required for all aggregates used outside of a data region unless the report contains exactly one data set.
Build complete -- 1 errors, 0 warnings

thanks, Sam
0

Commented:
I don't think the SUM is necessary.  Does this work?

=ROUND(Fields!JHVolume.Value, "DataSetTotal")*(Round(Fields!BartleyEngery.Value/Fields!BartleyVolume.Value,4, "DataSetTotal"))

Greg

0

Author Commented:
Greg, this is the error I get when I try that.

[rsCompilerErrorInExpression] The Value expression for the textbox textbox28 contains an error: [BC30519] Overload resolution failed because no accessible 'Round' can be called without a narrowing conversion:
Build complete -- 1 errors, 0 warnings
thanks, Sam
0

Commented:
Oops, you have to explicitly convert the values like this:

=SUM(CDbl(Fields!JHVolume.Value), "DataSetTotal")*sum(CDbl(Fields!BartleyEngery.Value)/CDbl(Fields!BartleyVolume.Value),"DataSetTotal")

Greg

0

Author Commented:
Greg, that work for my avg. but the sum is double.

thanks for the help.
Sam
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.