CODEWITHSUNDEEP
ssasmarginmdx
user1800552
user1800552

Reputation: 372

How to calculate margin percent in MDX

I have dimension which divides the single fact value by members. Ie. ([Dim].[Member1], value), ([Dim].[Member2], value) ..

Can i calculate dimension members margin with this single value?

Upvotes: 1

Views: 210

Answers (1)

user1800552
user1800552

Reputation: 372

Using calculated member formula as nested iif as:

CREATE MEMBER CURRENTCUBE.[Measures].[margin]
 AS    
    iif([Dim].[Member1] = [Dim].[Member1].&[X],
    sum([Dim].[Member1].&[X],[Dim].[Member1].&[Y], value) / ([Dim].[Member1].&[X],value),

    iif([Dim].[Member1] = [Dim].[Member1].&[Y],
    sum([Dim].[Member1].&[Y],[Dim].[Member1].&[F], value) / ([Dim].[Member1].&[Y],value),

    iif([Dim].[Member1] = [Dim].[Member1].&[F],
    sum([Dim].[Member1].&[F],[Dim].[Member1].&[Y], value) / ([Dim].[Member1].&[F],value),

    etc...

Formula is long, about 150 rows :)

Upvotes: 1

Related Questions