Problem with calculating values

I have problem with calculating values, let me give an example:

Fact table:

tableID docID authorID numMetadata numSubDocs

1 10 1 3 5

2 10 14 3 5

3 10 6 3 5

4 49 2 1 0

5 49 4 1 0

6 15 8 6 1

.....

I want to get in a report like this:

docID numMetadata numSubDocs

10 3 5

49 1 0

Grand total: 4 5

Calculated member: Sum(numMetadata) / Count(docID) works fine in rows except in grand total, because there should be sum of all rows. Can anyone help me write correct MDX or any other suggestion?

Couldn't you just do a count on the tableID? |||

no, in num... columns are values from rows of OLTP DB. Problem is that I need for every docID his value of numMetadata, but in fact table docID's are duplicated, and values in numMetadata are same for the same docID. So if I create a measure Sum(numMetadata), I also need a measure Count(docID), so that I can create calculated measure Sum(numMetadata) / Count(docID), so that I can have for each docID his numMetadata. That works fine, but not for grand total (in Excel). I hope somebody understands the problem?

I need calculated measure or something, that calculates every row in excel: Sum(numMetadata) / Count(docID) but on TOTAL rows there is only SUM of previous calculated values.

|||

Assuming that you're using AS 2005, you may be better off modelling 2 fact tables/measure groups, like:

New fact table at {DocID} granularity (no AuthorID), with numMetadata and numSubDocs measures (could be a Named Query)|||I was thinking about having physical table with num columns, but your idea of having named query is even better! Thanks