I am having an issue which is hopefully very simple to solve but I cannot
find the answer.

I have a list of employees:
Name Wage
A 10
B 20
C Null

I would like to calculate an average with MDX so that the average would be
15. (Sum of wages)/(Employees where Wage is not null)

I have figured out how to do the average
[Measures}.[Wage]/[Measures].[EmployeeCount], but this counts employee C.
How do I filter this out?

FYI, for other measures I need to see employee C, so I only need him
filtered from this average, not out of the table completely.

Thanks

use function Count(«Set»[, EXCLUDEEMPTY | INCLUDEEMPTY])

"Phil Swartzell" <PSwartzell1 (AT) comcast (DOT) net> schrieb im Newsbeitrag
news:#O6lZuNTEHA.4048 (AT) TK2MSFTNGP12 (DOT) phx.gbl...

Null fact table measure fields are treated differently by Analysis
Services, so a virtual cube may be best:

http://groups.google.com/groups?q=gr...qlserver.olap+
authoruri&hl=en&lr=&ie=UTF-8&as_drrb=b&as_mind=1&as_minm=1&as_miny=200
4&as_maxd=1&as_maxm=5&as_maxy=2004&selm=OW8KTKyJEH A.1132%40TK2MSFTNGP12.
phx.gbl&rnum=5
Subject: Re: Minimum Measure with NULLs
View: Complete Thread (2 articles)
Original Format
Newsgroups: microsoft.public.sqlserver.olap
Date: 2004-04-20 14:59:54 PST

This is a well-known issue when dealing with null data:

http://support.microsoft.com/default...b;en-us;244650
..
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:

Create multiple fact tables. One for each measure that needs a
distinction to be made between a NULL and a zero value.

Create a cube based on each fact table.

Create a virtual cube that includes all the base cubes to view all the
measures together.
In your case, presumably the fact records with NULL need to be excluded.
A simple solution would be to configure a SQL
"Source Table Filter" expression for the cube, like:

"FactTable"."Temp" is not null

- Deepak

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

It's true that Avg() ignores empty values. But, as the Microsoft KB
article states, these cells are treated as having a value of 0, not
empty (which is different).


- Deepak

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

You could create a view over the fact table and create a field to hold a value for 1 if the measure is not null and 0 if the measure is NULL. You could then create a measure based on the SUM aggregate and use that in your calculations. That's one option, but there are probably others as well

Sean Boo
Microsoft Office B

**This posting is provided AS IS, with no warranties, and confers no
rights.*