Average between different multiple dimensions

[jessbanaga]

Using Pivot table where dimensions W45 to W50 serve as dimension headers. I created two fact measures - Tot Hours and Avg. Hours  where Tot Hours expression SUM(Hours) which results the values from W45 to W50

Pivot Table

Cust	Categ	W45	W46	W47	W48	W49	W50
A	Tot Hours	40	30	40	40	20	50
A	Avg Hours	30	32.50	34	35	35.7	36.25

AvgTbl

Cust	Categ	W42	W43	W44	W45	W46	W47	W48	W49	W50
A	Avg Hours	20	30	40	40	40	40	40	40	40

Formula to get Avg Hours for the Categ column of the Pivot Table:

W45 = Average(AvgTbl.W42 to AvgTbl.W44) = 30

W46 = Average(AvgTbl.W42 to AvgTbl.W45) = 32.5

W47 = Average(AvgTbl.W42 to AvgTbl.W46) = 34

W48 = Average(AvgTbl.W42 to AvgTbl.W47) = 35

W49 = Average(AvgTbl.W42 ... AvgTbl.W48) = 35.7

W50 = Average(AvgTbl.W42 ... AvgTbl.W49) = 36.25

I tried this expression to get Avg Hours:

SUM(AGGR(AVG( {< [Week] = {"=[Week] >= $(vStartWeek) AND [Week] < $(vEndWeek)" }>} [Hours]), [Cust],[Week]))

Unfortunately it's not working.

The attached files are not exactly the same how it is laid-out here. But the context is very much the same.

[edwin]

look at your data again, you may have attached the wrong XLS.  looking at the file, average of week 42 to 45 is 30 not 32.5/  in fact all your running averages are all 30 since you have the same numbers for all the weeks (for Cust A).  sum for Cust A for all weeks is 60.