Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2004
    Posts
    6

    Unanswered: Calculate Average Analysis Servives

    Hi this might be pretty simple to you guys out there but i am having issues doing it.

    Please help!!!!!!!!!!

    Calculate Average of a measure called DIST irrespective of any dimension the page field.

    Ie. to say it should calculate the average for any doension i bring on the rwo field.

    Looking forward to your help.


    you can mail me at hem_k01@yahoo.com

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'm not sure anyone understands what you are trying to do.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jul 2004
    Posts
    6
    Dear Blind Man ,

    Thanks for your response

    Please let me know how to calculate average of a field in analysis dervices.

    I have written my issue in detail in the attachement.
    Attached Files Attached Files

  4. #4
    Join Date
    Jul 2004
    Posts
    6
    Dear Blindman,

    Thanks for you interest in my issue i was losing hope that anyone will reply.

    Sorry for not being clear.

    I have a cube with

    Four Dimensions .
    Location
    Product
    Time

    With measures
    DispKgs
    Ord Kgs
    Lines disp
    Lines ordered
    AIP IN KGS

    Calculated Measures
    Value fill %
    VALUE FILL Numerator
    Ordered Kg Reason,
    Ordered KG Location
    Ordered KG Product

    And
    Value Loss % This is the calculated measure where I am having an issue
    Hence Instead of having one single Calculated measure I have made three calculated measures which are Locn.Loss % and Prdt.Loss and Rsn .Loss.

    The reason is this.




    The Rsn Loss is given by the formula

    (([Measures].[Ord Kgs]*[Measures].[Aip In Kgs])-[Measures].[Value Fill Numerator])/([Measures].[Ordered KG Reason]*[Measures].[Aip In Kgs])

    Where Ordered KG Reason

    sum (descendants(Reason.members),[Measures].[Ord Kgs])/6


    I have divided it by 6 to adjust the values of Ordered KG Reason for Demo purposes.

    The Ordered Kg should be the total sum of Ord KGS for the dimension which is on the row field ( In this example Reason is on the row field) on an html page which uses pivot table (OWC Component) .

    The bottom line being that I want to calculate the sum of Ordered Kgs for whichever dimension is brought on to the row field and replace Prdt Loss ,Reason Loss and Location Loss by a single measure called value loss which depends on the sum of ordered Kgs of the row dimension .

    which will be possible if I can have just one ordered KG Measure instead of Ordered KG for every dimension.

    Lets say instead of reason dimension there was Location dimension the Ordered Kg Measure should be ale to calculate the sum of ordered kg measure for the location dimension. I,e the ordered kg measure should not have Dimension name hard coded in the formula like the one I have calculated.

    sum (descendants(Reason.members),[Measures].[Ord Kgs])/6



    Hope you can help me with this formula.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Sorry, but I have rarely used Analysis Services. I can't believe that calculating an average should be difficult, so hopefully somebody else on the forum can give you a hand.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Jul 2004
    Posts
    6
    Thanks for trying to solve it atleast blind man.

    You seem to be the only guy who was interested in helping me out.

    I habe not had anyone else replying so i duess i will have to look towards some other source.

    Thanks

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Sorry about that. Most question on this forum get answered, as you can see. But Analysis Services is not one of the more common topics here. Maybe other DBAs, like me, shun it because its implementation is kind of clunky. Its functionality does not live up to its hype. As you are finding out...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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