Report Builder - How to total percentages and find the average

Question

Report Builder - How to total percentages and find the average

lmd999

Old Hand

Points: 339
More actions
October 11, 2012 at 8:14 pm

#147432

Hi Everyone.
Hope someone here can help me out. I am new to Report Builder and Expressions.
I was able to calculate the percentage of the correct questions answered for each tester by using the following logic:
In SQL Query, for each right answer, I give it a "1". For each incorrect answer, I give it a "0". To get the % Correct for Tester A, I sum up the number of correct answers (1+1+1) and divide that by number of total questions, =CInt(Fields!TotalQuestions.Value.
TEAM A
======
Tester % Correct
------- ----------
A 75% (#correct answers/#total questions)
B 35%
C 50%
D 40%
E 45%
To derive at the values in the % Correct column, I use the following expression
=(Sum(CInt(Fields!CorrectAnswers.Value)))/(CInt(Fields!TotalQuestions.Value)))
How would I get the average %. What would the expression look like? I have tried something like =Avg(Sum(Sum(CInt(Fields!CorrectAnswers.Value)))/(CInt(Fields!TotalQuestions.Value)))) but it is off by a little.
Any help is much appreciated.
Thanks.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

robert.gerald.taylor SSChampion Points: 13133 More actions · Answer 1

lmd999 (10/11/2012)
Hope someone here can help me out. I am new to Report Builder and Expressions.
I was able to calculate the percentage of the correct questions answered for each tester by using the following logic:
In SQL Query, for each right answer, I give it a "1". For each incorrect answer, I give it a "0". To get the % Correct for Tester A, I sum up the number of correct answers (1+1+1) and divide that by number of total questions, =CInt(Fields!TotalQuestions.Value.
TEAM A
======
Tester % Correct
------- ----------
A 75% (#correct answers/#total questions)
B 35%
C 50%
D 40%
E 45%
To derive at the values in the % Correct column, I use the following expression
=(Sum(CInt(Fields!CorrectAnswers.Value)))/(CInt(Fields!TotalQuestions.Value)))
How would I get the average %. What would the expression look like? I have tried something like =Avg(Sum(Sum(CInt(Fields!CorrectAnswers.Value)))/(CInt(Fields!TotalQuestions.Value)))) but it is off by a little.

How is it off? a rounding difference? Why do you have Sum() twice in your expression?

Just asking,

Rob

lmd999 Old Hand Points: 339 More actions · Answer 2

Hi Rob,

Thank you for asking.

It is because I wanted to take an average of the Sum of all percentage values. Hope that helps.

lmd999 Old Hand Points: 339 More actions · Answer 3

I forgot to add that the resulting average is off by as little as 0.2% to over 1% in some cases. That is why I am wondering if the expression I am using is not summing up all the resulting percetages.

Thanks.