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.

    Please help.

  • Thom A

    SSC Guru

    Points: 98269

    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.

  • 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: 98269

    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)

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