Hi all,

id, column1, column2, column3
--------------------------------------------------------------------------
1 10 null 20

2 40 null 30

3 60 60 50


This is my fact table in data source . Values of column2 for row one and
two are null.

In the cube these null values treat as zero. That generate problem in my
average calculated member. in this example that returns s 60/ 3 = 20.

i want the average of not null values (60).

Please help me to solve this problem

Thanks in advance

Subin

just define a calculated measure that uses "iif" to replace null-values with
zero and calculate the average using this new calculated measure.

that should do.

"Subin" wrote:

I am afraid your suggesstion won't help here - since he already gets 0's
instead of NULLs. Perhaps you meant creating cell calculation which replaces
0's with NULLs ? This would work, but may have non-trivial performance
implications. In Yukon, you are able to define measures as NULLABLE, and
then NULLs from fact table don't become 0's in the cube.

--
==================================================
Mosha Pasumansky - http://www.mosha.com/msolap
Analysis Services blog at http://www.sqljunkies.com/WebLog/mosha
Development Lead in the Analysis Server team
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
==================================================

"RMerznich" <RMerznich (AT) discussions (DOT) microsoft.com> wrote

If you define a different base "Count" measure in the cube using column2
as Source Column (say, Column2Count), then Column2Count should be 1, so
that the Column2 average: Column2Sum/Column2Count = 60/1.



- Deepak

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