Sum and average over null values SQL Server 2008 Analysis Services

Question

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 values show as zeros.

Answer 1

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

Answer 2

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

Answer 3

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