Search code examples
reporting-servicesssrs-2008

How to get the average of multiple months of summed data in ssrs


I'm quite new to SSRS and having some difficulties with an expression. I've looked everywhere and can't find a similar problem. I have 13 months of data, each month may have multiple products of the same category that need to be summed before I get the average of all 13 months. I thought the expression at the bottom would work, however, it's only returning the sum of all DMD products. What I hoped it would do is sum all the DMD products for each month and return the average of all of the 13 summed months.

Expression: =AVG(Iif(Fields!BusinessDate.Value = Fields!BusinessDate.Value,sum(Iif(Fields!AcctType.Value = "DMD",Fields!Balance.Value, Nothing)), Nothing))

I imagine it's something to due with the dates that isn't working but I'm lost at this point.


Solution

  • I've figured it out after some playing around. I needed to sum the balances based off product and divide by the amount of distinct date records. This gives me what I need. Thanks to anyone who took a look and tried to help! The expression now looks like this:

    =sum(Iif(Fields!AcctType.Value = "DMD",Fields!Balance.Value, Nothing)) / CountDistinct(Fields!BusinessDate.Value)