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

problem with calculating sum of large nos

hello all
i am calculating sum of very large nos using sum function of SQL. but the final result i get is incorrect. i am using SQL server 2000.
like value of a field(cost) is calculated by multiplying .0018 to other fields (no of units)
the sum of (no of units) is 140374
but when i calculate the sum of (cost) it comes to 273.1600 which is incorrect \.
it shud have been 140374 * .0018
It is really difficult to pinpoint any problem with the information you provide. How does the actual query look like? What datatypes are used in the table?|||just for fun. . . .
Given the table is called UnitTable try this query:

select cast(.0018 as decimal(4,4)) * cast(sum([no of units]) as decimal(14,4)) from UnitsTable

does that give the right answer?