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
May	2006	$129,112	$21.56
June	2005	$100,449	$18.45
June	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

Answer this question

Average on a calculated column in matrix

SarikaPK
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.
amdMcBook
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
Marky52
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
Curtis Man
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.
Average on a calculated column in matrix

Answer this question