How to calculate average hours per week

[#1]

All worked hours are registered in our database. Hours are registered per week and per project.

I have created a pivot table that shows worked hours per week, and the total of worked hours for each project. So far so good.

Now I want to add an extra column that shows the average number of hours worked per week for each project.

The average should be taken of the total number of weeks registered (so regardless of the project!) If no hours were registered in a week than this week should not be used in the calculation.

See the Excel example for clarification.

Hope someone can help!

[Gysbert_Wassenaar]

See attached example.

[#3]

This is almost what I would like to see: I knew that the formula shouldn't be to difficult, but I couldn'd figure it out .

It would be nice if only the avg column at the right side of the table would be shown (see attachment, so no avg column for each week but only as a total), but I think this is not possible, is it?

[Gysbert_Wassenaar]

No, that's not possible. You could get such a column on the left by adding a calculated dimension. See attached example.

Edit: updated the example. QV is case sensitive so Week is not the same as WEEK. Use the exact field names and it'll work.

[#5]

It seems like it is what I need, but.. when I apply my 'real' data, no results are shown.

See attachment for Excel sheet and 'my' QV file. What am I doing wrong?