locked
MDX Expression - Different Rules at Different Levels RRS feed

  • Question

  • Hi All,

     

    I'm trying to figure out how to approach a calculated member with an MDX expression. Here's what I want to do.

    I have some existing calculated members (measures) figure out a rate represented as percentage. The problem is that the users think about these rates at their annual level (one of the inputs to this calc is semi additive). What I need to be able to do is scale them up to be indicative of the annual rate.

    So what I have now is this

    Year: 1.17%

       Quarter: 0.29%

    Month: 0.10%

    I need to scale these up as follows:

    Year: 1.17%

    Quarter: 1.16% (0.29*4 quarters)

    Month: 1.20% (0.1*12 months)

    Can anyone demonstrate how to alter the multiplying factor at different levels of a time dimension?

     

    Thanks in advance,

     

    Mark

     

     

     

    Wednesday, October 20, 2010 4:31 PM

Answers

  • SCOPE the calculated measure to override the values at Quarter and Month level.

    For example, if your measure is called [Measures].[Calculated Rate], you can use the following to override values at the Month level

    Scope 
     ( 
      [Date].[Calendar].[Month].Members,
      [Measures].[Calculated Rate]
     );     
      
      This = ( 
           [Date].[Calendar].CurrentMember,
           [Measures].[Calculated Rate]
         ) * 12;   
     
    End Scope;
    

    HTH

     


    -Remember to mark as helpful/the answer if you agree with the post.
    • Marked as answer by Mark Hickin Thursday, October 21, 2010 10:37 AM
    Wednesday, October 20, 2010 5:28 PM

All replies

  • SCOPE the calculated measure to override the values at Quarter and Month level.

    For example, if your measure is called [Measures].[Calculated Rate], you can use the following to override values at the Month level

    Scope 
     ( 
      [Date].[Calendar].[Month].Members,
      [Measures].[Calculated Rate]
     );     
      
      This = ( 
           [Date].[Calendar].CurrentMember,
           [Measures].[Calculated Rate]
         ) * 12;   
     
    End Scope;
    

    HTH

     


    -Remember to mark as helpful/the answer if you agree with the post.
    • Marked as answer by Mark Hickin Thursday, October 21, 2010 10:37 AM
    Wednesday, October 20, 2010 5:28 PM
  • Just what i was looking for, thanks
    Thursday, October 21, 2010 10:37 AM