Results 1 to 12 of 12
  1. #1
    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. #2
    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. #3
    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. #4
    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. #5
    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. #6
    Join Date
    Mar 2004
    Location
    Germany Düsseldorf
    Posts
    17
    No ideas?

    Please help me.

  7. #7
    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. #8
    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 ?
    Attached Files Attached Files

  9. #9
    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
    Hans.

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

  11. #11
    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. #12
    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
  •