Average on a calculated column in matrix

Hi,

i have some data i am showing in a matrix. The first row group is on month, the second row group is on year, in this way i get year on year reporting of my sales data.

One of the columns i have in the matrix is a "average $ per hour". The calculation for this works nicely on each row, i just get the total sales, and divide by the number of hours in that particular month (which i calculate). So it looks like this (these figures are invented):

Month Year Total sales Avg $ per hour May 2005 $123,456 $20.15 2006 $129,112 $21.56 June 2005 $100,449 $18.45 2006 $130,889 $20.57 Total $483,906 $20.10

Now i need to also put a average dollar per hour into the total row, but i cannot work out how to calculate the number of days covered by all the months listed in the matrix. Is there a way i can calculate this with an expression on the report, or will i have to resort to calculating the days per month as part of the mdx query that gathers the data?

Thanks!

sluggy

How do you calculate the number of hours in a particular month? If it is a calculated field in the dataset, you can change the expression you use for the average $ per hour column to be =Sum(Fields!Sales.Value)/Sum(Fields!Hours.Value). The value of Sum(Fields!Hours.Value) in the detail cell would be the hours for the particular month year, the value of it in the total cell should be the hours for the entire matrix.|||

Hi Fang,

the calculation to get the number of hours in a month is performed inline in the report, it is:

Sales / (24 * DateDiff("d", CDate("2006-" + Fields!Calendar_Month.Value + "01"), DateAdd("M", 1, CDate("2006-" + Fields!Calendar_Month.Value + "01"))))

(i know this doesn't allow for Feb, that is still to be fixed :) ). In the detail rows, this works nicely, but i can't see a way to implement this type of calculation in the total row.

What would be the best way to do this?

Thanks,

sluggy

|||For the total row, you'll need to sum together the hours for all the months and eliminate the duplicate periods. One way to do is through custom code. Suppose the custom function is called GetHours(), you'd call it in the expression in the total cell like this: =Sum(GetHours(Fields!Calendar_Month.Value, Fields!Calendar_Year.Value)). Inside the function, you can check the current month/year against a look-up table (which keeps track of the different periods you've seen), if it's a dup, return 0, if not, return the hours for that month.|||

Hi Fang,

thanks, that's the approach i ended up taking, here was the result (i thought i had a bug, but it was my own fault).

sluggy