Struggling Creating Calculated Measure

  • I’m struggling implementing a calculated measure in one of my cubes. Basically I need to take two measures, divide them for a given period, and them sum the results together. For example, if the user selects 2012 as the year, I need to do the following:

    (Q1, Q2, etc. are children under 2012 in my date.calendar dimension hierarchy)

    (Q1 Purchases / Q1 Base) +

    (Q2 Purchases / Q2 Base) +

    (Q3 Purchases / Q3 Base) +

    (Q4 Purchases / Q4 Base) +

    But this needs to work dynamically based on whether the user selects multiple years, a single year, a single quarter, independent quarters for any given years, etc. Anyone have any ideas?

    Thanks in advance,

    Jason

  • Replace [Internet Order Count] with base

    and [Measures].[Customer Count] with purchases

    from the below example.

    I have created a [hidden measure] which calculates the purchase/base for every date member.

    The [RequiredMeasure] with sum the children which will result in the calculation like you requested.

    CREATE MEMBER CURRENTCUBE.[Measures].[HiddenMeasure]

    AS null,

    VISIBLE = 1 ;

    SCOPE([Date].[Calendar].MEMBERS ,[Measures].[HiddenMeasure]);

    THIS=iif([Measures].[Internet Order Count]=0,NULL,[Measures].[Customer Count]/[Measures].[Internet Order Count]) ;

    END SCOPE;

    CREATE MEMBER CURRENTCUBE.[Measures].[RequiredMeasure]

    AS null,

    VISIBLE = 0 ;

    SCOPE([Date].[Calendar].MEMBERS ,[Measures].[RequiredMeasure]);

    THIS=sum([Date].[Calendar].currentmember.children,[Measures].[HiddenMeasure] );

    END SCOPE;

    Hope it helps

  • moviesvj, thanks for the reply. Works great!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply