SSRS Including empty cells when calculating avg.

Question

Group, This is hard to explain, but I am building a matrix report in SSRS. My row includes a product code, my columns are months (previous 13) and my values are units sold for the corresponding product/month. I found some custome code that would let me change my subtotal to Avg However, for some products there might not be any units sold for a month so the cell is blank.

What is happening is say for product code X I sold 10 units in March 2009 and 1 unit in January 2010 for a total of 11. Instead of taking 11(units sold) / 13(months) it is taking 11 / 2 because it is only registering 2 months bbecause there aren't any values in the other 11 months because there were no units sold.

I tried formatting the field using CInt which placed zeros in the empty cells, but it still only includes cells that have a number > 0 as the divider... For products that have a sale in all 13 month the avg caluclates just fine.

I am not sure how I can tell it to take the total and divide it by 13(months) even if there are no values for a particular month.

Any help is greatly appreciate.

Answer 1

I'm basing my answer on this custom code for getting the average as described by Tom VdP here. This gets the average as calculated by SSRS. However, you can calculate your own "average" because

Average = Sum / Count

For your purposes, the count is always 13. The custom code method gets you the sum, so you can ignore using SSRS to calculate the average and simply substitute the average (avgValue in the Tom's code) with

sumValue / 13