none
Aggregating calculated measures

    Question

  • Hello,

    I'm having trouble getting a calculated measure to aggregate properly. It's defined as:

    [Measure A] x [Measure Percent]

    where [Measure Percent] is only defined where we have both [Dimension X] and [Dimension Y] (there are several other dimensions). This might be solved with a derived measure, as per other responses in this forum, but [Measure A] itself is a calculated measure and it uses functions that can't be evaluated at the leaf level (e.g. ABS). I've tried something like this:

    CASE
    WHEN
        IsLeaf([Dimension X].CurrentMember) And IsLeaf([Dimension Y].CurrentMember)
      THEN
        [Measures].[Measure A] * [Measures].[Measure Percent]
    WHEN
        Not IsLeaf([Dimension X].CurrentMember) And Not IsLeaf([Dimension Y].CurrentMember)
      THEN
        Sum(
          NonEmptyCrossJoin(
            Existing [Dimension X].Children,
            Existing [Dimension Y].Children),
          [Measures].[Measure A] * [Measures].[Measure Percent]
        )
    END

    This gives the right answer for the Grant Total and where we have [Dimension X] and [Dimension Y] in the pivot, but not for subtotals (of course) and not when we deselect some members of [Dimension X] or [Dimension Y]. The former can be fixed with two extra WHEN clauses, but I can't find a way round the latter - it always returns the sum across all members of [Dimension X] and [Dimension Y], even when some are excluded. Maybe the sub-selects that the MDX query from Excel 2010 uses aren't accessible at the point that the measure is calculated. Is there a simpler way to do these calculations? And is there a way to reliably calculate the Grand Total and subtotals whatever has been selected?

    Thanks,

    Steve

     

    Monday, September 19, 2011 11:38 AM

Answers

  • Hi Steve,

    Here's what you need to do:

    • In your fact table, or in a view on your fact table or in the DSV, add an extra column that simply returns a null value.
    • In your cube, build a new real (ie not calculated) measure with AggregateFunction set to Sum based on this new measure. Call this new measure Z.
    • Deploy and process your cube. At this point measure Z will only ever return null values.
    • Then go to the calculations tab in the cube, and use a scoped assignment to do your calculation at the lowest granularities of Dimension X and Y. the scoped assignment would look something like this:

    SCOPE(MEASURES.Z);

    SCOPE([Dimension X].[Key Attribute of Dimension X].[Key Attribute of Dimension X].MEMBERS);

    SCOPE([Dimension Y].[Key Attribute of Dimension Y].[Key Attribute of Dimension Y].MEMBERS);

    THIS = [Measures].[Measure A] * [Measures].[Measure Percent];

    END SCOPE;

    END SCOPE;

    END SCOPE;

     

    This will perform the calculation at the leaf level, and then, because you're assigning the value of the calculation at the leaf level to a real measure that Sums up, the result of the calculation will sum up correctly for all your queries. And even more importantly, it will perform better than the approach you're using right now.

    HTH,

    Chris


    Blog: http://cwebbbi.wordpress.com/ Consultancy: http://www.crossjoin.co.uk/
    • Proposed as answer by Javier Guillen Monday, September 19, 2011 11:58 PM
    • Marked as answer by SteveHicks Tuesday, September 20, 2011 3:52 PM
    Monday, September 19, 2011 12:08 PM
    Moderator

All replies

  • Hi Steve,

    Here's what you need to do:

    • In your fact table, or in a view on your fact table or in the DSV, add an extra column that simply returns a null value.
    • In your cube, build a new real (ie not calculated) measure with AggregateFunction set to Sum based on this new measure. Call this new measure Z.
    • Deploy and process your cube. At this point measure Z will only ever return null values.
    • Then go to the calculations tab in the cube, and use a scoped assignment to do your calculation at the lowest granularities of Dimension X and Y. the scoped assignment would look something like this:

    SCOPE(MEASURES.Z);

    SCOPE([Dimension X].[Key Attribute of Dimension X].[Key Attribute of Dimension X].MEMBERS);

    SCOPE([Dimension Y].[Key Attribute of Dimension Y].[Key Attribute of Dimension Y].MEMBERS);

    THIS = [Measures].[Measure A] * [Measures].[Measure Percent];

    END SCOPE;

    END SCOPE;

    END SCOPE;

     

    This will perform the calculation at the leaf level, and then, because you're assigning the value of the calculation at the leaf level to a real measure that Sums up, the result of the calculation will sum up correctly for all your queries. And even more importantly, it will perform better than the approach you're using right now.

    HTH,

    Chris


    Blog: http://cwebbbi.wordpress.com/ Consultancy: http://www.crossjoin.co.uk/
    • Proposed as answer by Javier Guillen Monday, September 19, 2011 11:58 PM
    • Marked as answer by SteveHicks Tuesday, September 20, 2011 3:52 PM
    Monday, September 19, 2011 12:08 PM
    Moderator
  • Try using the scope instruction :

     

    SCOPE(LEAVES(),[Measures].[yourname]);

     

    this= [Measures].[Measure A] * [Measures].[Measure Percent] ;

     

    END SCOPE
     
    Another method that I use is to calculate this in the facttable, this way it's correctly aggregated in the cube.
    Hope this helps,
    Pierre
    Monday, September 19, 2011 2:42 PM
  • Hi Chris,

    That seems to do the trick - thanks very much!

    Steve

    Tuesday, September 20, 2011 4:38 PM
  • Hi,

    I was using the 'real measure and overwritten by scope assignment' approach to get a measure called visits, which basically count multiple number of transactions of a customer into 1 visit if the transactions happen on the same day:

    SCOPE(MEASURES.Visits,[Customer].[Customer ID].[Customer ID].MEMBERS); 
        THIS = COUNT( 
                EXISTING NONEMPTY(
                        [Date].[Date].[Date].MEMBERS,
                        [Measures].[Transaction Count]
                        )
               );
    END SCOPE;


    And I did assign the SUM aggregate function for the visits measure via BIDS, after deploy and process the cube, it appears to be very slow, hope somebody can shed some light here.


    -- Let us help each other to improve continuously.


    Wednesday, September 19, 2012 4:11 AM
  • Hi Minnie,

    Does the following perform better and do what you want?

    SCOPE(MEASURES.Visits,[Customer].[Customer ID].[Customer ID].MEMBERS, [Date].[Date].[Date].MEMBERS); 
        THIS = IIF(ISEMPTY([Measures].[Transaction Count]), NULL, 1);
    END SCOPE;

    Regards,

    Chris


    Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK

    Wednesday, September 19, 2012 7:07 PM
    Moderator
  • Hi Chris,

    Yes, it does what I wanted and actually it makes more sense to do this way.

    The performance does get better (for certain query it become from 4 minutes to a little over 1 minute) and I still need to fine tune on it, maybe check aggregation.

    Thank you.

    Regards,

    Min


    -- Let us help each other to improve continuously.

    Thursday, September 20, 2012 12:50 AM
  • Hi Steve,

    what I usually do is calculating in the DSV.

    Then it's aggregated naturally in the cube.

    Easy and efficient.

    Pierre

    Thursday, April 18, 2013 1:36 PM