Friday, February 10, 2012

Calculated member question

I have a problem creating a calculated member correct.

Business problem: I have an organisation using MS Project and registering their project time against different projects (cube 1). Every organisation unit part of a project will be charged cost internally based on an agreement with a percentage split (write enabled cube 2). The project "customers" are the same organisation units as the participant organisation units.

Cube 1: Projects and customer cost

Cust1 Cust2
Proj1 100 200 (total project cost:300)
Proj2 10 50 (total project cost: 60)

Cube 2: Project charging %

Cust 1 Cust2
Proj1 50% 50%
Proj2 40% 60%

Now I want cube 3 (virtual cube) calculate how much each customer should be charged;

Cube3: Project cost "invoice"

Cust 1 Cust 2
Proj1 150 150 (50% of 300)
Proj2 24 36

But if I in cube 3 add a custom member like this:

[Measures.Cost] x ([Measures.Percentage] / 100)

I end up with the wrong result:

Cust 1 Cust 2
Proj1 50 100 (50% of 100 and 200 from cube 1, not based on 300)
Proj2 4 30


Can anyone help? How should I write the formula?

/Erik

Try somehting like this...

([Measures.Cost],[Customers].[Customers].[All]) x ([Measures.Percentage] / 100)

...basically telling the MDX to get the Cost for All customers, not to use the current customer context.

No comments:

Post a Comment