# Thread: Total sum of a measure

1. Registered User
Join Date
Mar 2004
Location
Germany Düsseldorf
Posts
17

## Unanswered: Total sum of a measure

Hi,

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!

Best regards,
Thorsten

2. Registered User
Join Date
Apr 2004
Posts
13
You will need to explain this a little more clearly

Show some examples in maths terms what you are trying to achieve

3. Registered User
Join Date
Mar 2004
Location
Germany Düsseldorf
Posts
17
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.

4. Registered User
Join Date
Apr 2004
Posts
13
You are not making yourself clear

Are you talking about - length (wood, steel, ???, volume (water), speed , sound decibals, what what what??????)

WHAT MEASURE IS IT YOU WANT

MAKE YOURSELF CLEAR AND DO NOT TALK IN PROGRAM CODE

TRY TO SHOW AN EXAMPLE

For example

I have a Carton containing 12 bottles
A pallet hold 36 Cartons
There fore the number of bootles on a pallet is 12 x 36

Ralk in english - not code - it is not clear what you are trying to do

WHAT ARE YOU MEASURING ??????

5. Registered User
Join Date
Mar 2004
Location
Germany Düsseldorf
Posts
17
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.

6. Registered User
Join Date
Mar 2004
Location
Germany Düsseldorf
Posts
17
No ideas?

7. Registered User
Join Date
Apr 2004
Posts
11
Hi ThorstenB,

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?

Ta
Paul

8. Registered User
Join Date
Mar 2004
Location
Germany Düsseldorf
Posts
17
Hi EOLAP,

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:
Dim1- Value
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.

ok ?

9. Registered User
Join Date
Mar 2004
Posts
45
Something like this?

CREATE TABLE Complaints(
ArticleID smallint PRIMARY KEY,
PercentComplaints smallmoney)

INSERT Complaints
SELECT 1,30
UNION ALL SELECT 2,60
UNION ALL SELECT 3,10

CREATE TABLE ArticlesOwners(
ArticleID smallint,
OwnerID smallint,
PercentOwned smallmoney,
PRIMARY KEY(ArticleID,OwnerID))

INSERT ArticlesOwners
SELECT 1,1,33.3333
UNION ALL SELECT 1,2,50
UNION ALL SELECT 1,3,16.6667
UNION ALL SELECT 2,4,50

SELECT c.ArticleID, x.OwnerID, STR(PercentComplaints * PercentOwned / 100, 2) + '%' Blame
FROM Complaints c JOIN ArticlesOwners x ON c.ArticleID = x.ArticleID

10. Registered User
Join Date
Mar 2004
Location
Germany Düsseldorf
Posts
17
Sorry, this is SQL. What I need is MDX.

11. Registered User
Join Date
Apr 2004
Posts
11

## :-)

Ok,

Thank you for the attachment!

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'
SELECT ....

but replace the [COMPLAINTCOUNT]/1.5 with the proper calculation.

Ta
Paul

12. Registered User
Join Date
Mar 2004
Location
Germany Düsseldorf
Posts
17
Perhaps I didn´t understand you, but what is the "proper calculation" in my sample? I want to have the percentage measure in the cubefile.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•