SSAS Cube Calculation

Question

I have a cube that consists of several dimensions and metrics. I have a metric 'cost' and i would like to get a percentage cost for the record over the total cost based on the segments that the user selects. For example:

If the user selects dimension: 'Campaign', 'Adgroup' and 'Keyword', the result set should calculate the '%cost' as below. All '%cost' should add up to 1.

If the user selects dimension: 'Campaign', the result set should calculate the '%cost' as below. All '%cost' should add up to 1.

How am i able to write a formula in SSAS cube calculation to enable me to dynamically calculate the %cost based on the segment a users selects?

I have the current formula as ([Keyword].[Keyword].CurrentMember, [Measures].[Cost])/ (sum(Axis(1),[Measures].[Cost])). It works but takes long time to calculate. I am wondering if there is a better way to do this.

Thanks in advance.

Answer 1

What if to use Parent?

[Measures].[Cost]
/
(Axis(1).Item(0).Item(0).Hierarchy.CurrentMember.Parent
,[Measures].[Cost])

This lets server use pre-calculated value instead of SUM.

(I've also deleted [Keyword].[Keyword].CurrentMember since don't see reason to use it here: if it's on axis or as filter, engine will apply it automatically)

Answer 2

This formulae Works perfectly. But when in a cube you have more tan two and try to show them in Excel it shows #value error instead of giving the result while if placing more than two variables it Works. That is the only problema.