Hello:
I am trying to compute the Averages of the Sales_amounts, Sales_Margins,
Sales_Markups.

These measures are present in the Fact Table. I've dimensions are as
usual--based on
geography,
Time,
Tool_Type,
Tool_Purchase_Type.

The only caveat is that there could be some nulls for records in
Sales_Amounts, sales_Margins or even in Sales_Markups and also sometimes Zero
as the value. I shouldn't consider the records with NULL values but should
consider the cells with Zero for counting and for averages. If I consider the
cells with Nulls, that would distort my averages.
I tried to use count(Geography_ID) in member properties and populated a
field in Measures table, but I believe that count takes into account even the
cells with Null value. So, this doesn't help me at all.
I computed Count = count(Geography_Id) separately and then computed
average using
[Measures].[Sale Amount]/[Measures].[Count].

Can I anyway use
Avg(Descendants([Time].[2005],Quarter]),Measures.Sales_Amount).

It gives me a syntax error. I guess I'm going wrong some where.

I read about ExcludeEmpty option in Count, but faced with syntax problems.
I guess I had somewhere wrong.
Any help is highly appreciated.

Thanks,
Bob

The Microsoft KB INF article below addresses this issue. In your
specific scenario, you can try creating a cube 'count' measure on each
of the 3 fields: Sales_amounts, Sales_Margins and Sales_Markups. From
what I recall, each count will exclude nulls for its underlying fact
field.


http://support.microsoft.com/default...b;en-us;244650

SUMMARY

A NULL value, in OLAP Services, represents an empty cell. In relational
products such as Microsoft SQL Server 7.0 or later, a NULL value
represents an unknown value. In online analytical processing (OLAP) a
NULL value means that we know the cell is empty.

Question: How does OLAP Services read NULL measure data in a fact table?

Answer: If there is no record in the fact table for a certain
combination of dimension members, (such as [1997].[Quarter1].[January],
[USA].[Texas].[Dallas] ) then, on cube processing, OLAP Services treats
the corresponding cell in the cube as empty. However, when there are
records in the fact table that have one or more null measure columns,
OLAP Services assigns a 0 value to the corresponding cells in the cube.

This behavior may not always be desirable, particularly, when the user
has to make a distinction in the cube's measure between a fact table
NULL and zero values. If such a distinction must be made, then the user
has to make sure that the fact table (or tables) do not have records
with empty measure columns. One solution is to break these empty measure
columns into different fact tables. The steps necessary to implement
this solution are:
1. Create multiple fact tables. One for each measure that needs a
distinction to be made between a NULL and a zero value.
2. Create a cube based on each fact table.
3. Create a virtual cube that includes all the base cubes to view all
the measures together.

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***