Sum and average over null values SQL Server 2008 Analysis Services

I have a simple problem, I think, but I have googled and can't find the solution. I have a cube that has MeasureA, MeasureB and MeasureC. Not all three measures have values for each record, sometimes they can be null, it's depending if it was applicable.

Now for my totals, I need to average but the average must not take nulls into account. Any help will be much appreciated. When I view the measures, the null val开发者_Python百科ues show as zeros.

Why not compute the average without the use of the AVG() function?

For example:
([MEASURES].[MEASUREA] + [MEASURES].[MEASUREB] + [MEASURES].[MEASUREC]) / 3

Of course, this only works if this average will only ever consist of 3 measures. I would only really use the AVG() function if you needed to evaluate over a set:
http://msdn.microsoft.com/en-us/library/ms146067.aspx

By default, SQL Server ignores nulls - hence the message you get - Warning: Null value is eliminated by an aggregate or other SET operation.

Example:

create table #test (id int null)
insert #test values (1)
insert #test values (1)
insert #test values (null)
insert #test values (1)

select avg(id), sum(id), count(id) from #test

Can you use the NonEmpty() MDX function to wrap the sets that you are averaging out?

http://msdn.microsoft.com/en-us/library/ms145988.aspx

For each measure, in its Properties go to the source column settings and choose NullProcessing="Preserve", and then the measure will return NULL and not 0