May 27, 2016 at 6:35 am
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.
Please help.
May 27, 2016 at 6:55 am
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):
Your maths:
[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.
Larnu.uk
May 30, 2016 at 1:55 am
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.
May 30, 2016 at 11:32 am
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.
Larnu.uk
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply