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.
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)