I have created a calculated member in an Analysis Server Cube that rolls up
the [Reg Population] colum (stored as s decimal) on Voting District Id level

The Hierarchy : All

The desired result should be that the "Province Name" level should sum up
all its descendants (Municipality Names) but the actual figures below show
that there is one less on province level that its descendant municipality
name level.

Province 1 22603
Municipality 1 22604
Ward 1 2897
Ward 2 5024
Ward 3 3490
Ward 4 2834
Ward 5 4349
Ward 6 4010

The MDX below was used to generate the figures above, but how can a higher
level have less than the level below it ?



iif (
Delimitation.currentmember.level.name = "Voting District Id",
[Measures].[Reg Population],

iif (
Delimitation.currentmember.level.name = "Ward Id",
round(sum( descendants( Delimitation.currentmember,1),
[Measures].[Reg Population] ), 0),


iif (
Delimitation.currentmember.level.name = "Municipality Name",
sum( descendants(delimitation.currentmember,1,self )
,round([Measures].[Reg Population],0) ),


iif (
Delimitation.currentmember.level.name = "Province
Name",
sum( descendants(
delimitation.currentmember,1,self ), round([Measures].[Reg Population],0) ),


iif(
Delimitation.currentmember.level.name = "(All)",
sum( descendants(
Delimitation.currentmember,1,self),round( [Measures].[Reg Population],0 ) ),
sum( descendants(
Delimitation.currentmember,1,self),round( [Measures].[Reg Population],0 ) )
)
)
)
)
)
--
MCBBA, MCTS, MCSE

One possiblity is that it could be the rounding, sometimes you are
summing and then rounding, other times you are rounding then summing.

eg

Raw Rounded
0.8 1
0.8 1
0.8 1
=== ===
2.4 2 Total (Sum then round)
3 Total (round then sum)

--
Regards
Darren Gosbell - SQL Server MVP
Blog: http://www.geekswithblogs.net/darrengosbell

In article <06DD5E06-060A-401C-B682-8424990246B7 (AT) microsoft (DOT) com>,
SQLBerndt (AT) discussions (DOT) microsoft.com says...

Thanks Darren,
It is actually a level issue. I am now forcing the descendants function to
go 1,2 or 3 levels down , and that solved the problem.

I have included the updated MDX which now works.

Thanks for the response





iif (
Delim.currentmember.level.name = "Voting District Id", [Measures].[Reg
Pop],

iif (
Delim.currentmember.level.name = "Ward Id",
round(sum( descendants( Delim.currentmember,1), [Measures].[Reg
Pop] ), 0),


iif (
Delim.currentmember.level.name = "Municipality Name",
sum( descendants(Delim.currentmember,1,self )
,round([Measures].[Reg Pop],0) ),


iif (
Delim.currentmember.level.name = "Province Name",
sum( descendants( Delim.currentmember,2,self ),
round([Measures].[Reg Pop],0) ),


iif(
Delim.currentmember.level.name = "(All)",
sum( descendants(
Delim.currentmember,3,self),round( [Measures].[Reg Pop],0 ) ),
sum( descendants(
Delim.currentmember,3,self),round( [Measures].[Reg Pop],0 ) )
)
)
)
)
)
--
MCBBA, MCTS, MCSE


"Darren Gosbell" wrote: