for a calculated measure I need the total sum of a measure in different dimension. So for example I want to get in my first dimesion 1500 as my sum and in my second dimension too. Is there a way to get it?
(FirstDimension.CurrentMember.Parent, [Measures].myMeasure) doesn´t work!
I want to create a calculated member which shows the percentage allocation of a measure in each dimension. So the expression "(FirstDimension.CurrentMember.Parent,
[Measures].myMeasure)" must yield the same result the same for the first and second dimension.
For example, I sum complaint costs. Every complaint has an article and a responsible user. The first dimension shows the percentage allocation of the cost for the articles. Then I want to expand the article xyz and show the percentage allocation of the cost for the responsible user, who is responsible for the complaints for the article xyz. So the second dimension represents the responsible user. In this dimension I need the total cost value from the first dimension in order to put it into relation to the cost of the user abc and the article xyz.
I think some of the confusion has arisen because you are saying Dimension when you mean Measure. However, having read through you last paragraph, what you are trying to do sounds resonably complex - could you give some numeric examples of what you would expect to see?
in the attachment you will find a sample application which describes my problem. Start it in VB and run the 3 queries.
The first query shows the desired result.
The second query shows an error in the percent column.
When running the third query I expect the result shown in the picture "Show article and type.jpg".
Here a numeric sample:
M1 - - 30%
M2 - - 60%
M3 - - 10%
--------------------------- expand M1
Dim1 Dim2 - Value
M1 - - B1 - - 10%
M1 - - B2 - - 15%
M1 - - B3 - - 05%
M2 - - B4 - - 30%
The percentage values for the members in the 2nd dimension refer to the total data set.
The problem is that you have a measure that is a %. the dodgy values you are getting is due to Analysis Services summing up the percentages. You should use a calculated member to create the % value and drop it as a fixed measure.
Use something like...
with member measures.mypercentage as '[Measures].[COMPLAINTCOUNT]/1.5'
but replace the [COMPLAINTCOUNT]/1.5 with the proper calculation.