Dear all,

I created a calculated member using formula as below, purpose is to set
the value=0 if the dimension member of Gender = 'N/A'.

Iif([Census Gender].CurrentMember is [Census Gender].[Gender All].&[All

Gender].&[N/A], 0,
[Measures].[Census Pop])

It is successfully display 0 for gender = 'N/A'
however, the rollup sum of this calculated measure for dimension
'Gender' is still sum of all original values of different gender
including 'N/A'.

Is this approach not workable? Is there anyway to tackle this ?
Thanks in advance.

Assuming that [Measures].[Census Pop] has a 'sum' aggregation function,
try the following:

[Census Gender].[Gender All].&[All Gender].&[N/A],
0,
Iif(IsAncestor([Census Gender].CurrentMember,
[Census Gender].[Gender All].&[All Gender].&[N/A]),
[Measures].[Census Pop] - ([Measures].[Census Pop],
[Census Gender].[Gender All].&[All Gender].&[N/A]),
[Measures].[Census Pop]))

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Thanks for your reply. It works for Gender
However, [Measures].[Census Pop] is not only for dimension Gender, say this
measure is also for Age. sample as below:

Gender Census Pop
------- --------------
M 100
F 200
N/A [0]

Age Census Pop
----- ------------
10-20 50
20-30 100

So any combination of Gender and Age result 0.
How can I tackle this? Please advise.
Thanks in advance


"Deepak Puri" wrote:

If multiple dimensions have "N/A" members, should the contributions not
be rolled up for cells where the member of any dimension is "N/A"? So,
if Measures].[Census Pop] is 50 for Gender = "M" and Age = "N/A", should
50 be excluded in Age roll-up, but included in Gender rollup? Some
examples of fact data with desired results may help, since it seems more
complex than the original description; and Calculated Cells may be a
better solution than Calculated Members.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

You are right. The fact table actually has total 6 dimensions and 2 measures,
just pick 2 dimensions as example:

Gender Age Region CensusPop CustCnt
-------- ---- -------- ------------- ---------
n/a n/a A 30000 120
F n/a A 100 0
M n/a A 200 0
n/a 10-20 A 50 0
n/a 20-30 A 100 0
n/a > 30 A 150 0

The record for age is n/a and gender is n/a cannot be ignored in fact table
because it is used for another dimension and another measure.

So in the cube if select Gender = 'F' and Age = 'n/a', the Census Pop should
be 100.
If select Gender = n/a and Age = n/a the Census Pop should be 0
If select Gender = n/a and Age = >30, the Census Pop should be 150
In any case, the total of Census Pop should always be 300.

Please kindly help.


"Deepak Puri" wrote:

At least from your sample, the fact data seems to be summarized only
along single dimensions. So there is no data for combinations of
different dimensions, such as for Gender F AND Age 10-20. Typically you
would start with fact data of a higher dimensionality for a cube, so is
that level of detail (ie. Census Pop and Cust Cnt for different
dimension combinations) not available?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Yes. You are right, census pop and cust cnt is not available for detail
Gender AND detail Age combine together. But both measures are available for
another dimension 'Region' with hierarchy 'Region -> District -> Street
Block'.
i.e. The result is if select a detail level of Street Block, both census pop
and cust cnt are available for comparison. For other dimensions like Gender
and Age, I can only analyze each dimension individually for census pop.

The purpose of this cube is let user has an idea of penetration rate of
customers in different 'region' with additional information of population of
different dimensions in different region.

"Deepak Puri" wrote: