MDX expressions to aggregate data at certain level for calculated member

Question

For now sometimes I have problems with creating difficult calculated members in SSAS. Is it possible to make case which will SUM certain measure on certain level when user choose another certain level of dimension? For example we have standard time dimension with 4 levels:

Year Month Week Day

Also we have some measure orders which have default function SUM in properties.

Which case do we need to calculate this: sum all orders in week which including current day which we have chosen already.

Also could you recommend me some nook or source for level up my mdx knowledge?

Thanks a lot.

Answer 1

Yes, it's possible, when you use MDX functions, which are connected with levels.

Here is an example with Year > Quarter > Month > Day hierarchy:

Use MDX calculated member to have a SUM of upper level member children (including selected one):

The same if you want to create calculation inside the cube:

CREATE MEMBER CURRENTCUBE.[Measures].[SameLevelMembers]
as SUM({[Report Date].[Report Date].CurrentMember.Parent.Children},[Measures].[Count]),
VISIBLE = 1 ;

And a result in cube browser:

Hundreds of articles you can find there: http://ssas-wiki.com/w/Articles#MDX

I also like this one: http://mdxpert.com because of well-structured info.