# average not working

• Nomvula

Hall of Fame

Points: 3770

Hi guys

I have a following scenario on my report that I'm trying to get the average on:

A. 1.5/(11-0) = 14%

B. 0/(11-5.5) = 0%

I need to average A and B inorder to get 7%

to do that I've added up the two : 1.5/(22-5.5) of which I'm getting 9% which is incorrect.

I can't add 14% & 0% because it's already a calculated sum field.

• Thom A

SSC Guru

Points: 98515

I hate to say it, but your 9% is correct, for the maths you've supplied at least. You're creating an overall % using that formula, not an average of your results (sorry I'm a maths lesson here):

[font="Courier New"](1.5 + 0) / ((11-0) + (11- 5.5)) = 1.5 / (11 + 5.5) = 1.5 / 16.5 = 0.09 = 9%[/font]

What you're going to be looking for is:

[font="Courier New"]((1.5 / (11-0)) + (0 / (11-5.5))) / 2 = ((1.5 / 11) + (0/5.5)) / 2 = (0.136 + 0) / 2 = 0.068 = 6.8% ˜ 7%[/font]

This is a little harder without your code, but I'm assuming you have a cell in SSRS with something like (I have named your three columns [Base], [Finish], [Start]:

`=Fields!Base.Value / (Fields!Finish.Value + Fields!Start.Value)`

You could, therefore, simply put an AVG() around this formula in your expression in your totals grouping. This will give you the 7% you're looking for.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

• Nomvula

Hall of Fame

Points: 3770

Hi Tomm

thanks for the response, is there a way to simplify your calculation in order to give me the sum in one line in the report.

eg. adding up row A & B to give the total sum instead of breaking it down like you did.

• Thom A

SSC Guru

Points: 98515

It might be easier to show you if you provide some sample data. Can you provide a create statement with some fictitious data, should help me to explain better, won't some screenshots on SSRS.

Cheers!

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

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