When a calculated measure uses a function that requires a dimension reference, the results of the calculation can be impacted by the VisualTotals function. This behavior is similar to the issue described in SAS Note 39955. The example below demonstrates this behavior and provides an explanation of the results.

WITH 
  MEMBER [Measures].[My Calculation] AS 
        AGGREGATE(YTD() ,[Measures].[ActualSum] )
 
SELECT 
  {[Measures].[ActualSum], [Measures].[My Calculation] } ON COLUMNS,
  VISUALTOTALS(ADDPARENTS(
    CROSSJOIN(
      CROSSJOIN(
        [Time].[All Time].[1993].children,
        [GEOGRAPHY].[All GEOGRAPHY].[CANADA]
        ),
        [PRODUCTS].[PRODTYPE].members
), POST), "Total *") ON ROWS
FROM 
  [SalesCube]

The unexpected values occur because of the following:

When more than two dimensions are placed next to each other on an axis (rows, in this case), the subtotal for the innermost dimensions are crossed with an actual member of the outer dimensions. The actual member of the Time dimension exists in this case and is the set generated by YTD, which is not empty. That fact means the calculation will aggregate the totaled value over that set returned by YTD.

These actions are taken because of solve order (see SAS Note 40007). Solve order is defined as "A higher number indicates that the member is calculated using the values that result from the calculations that have a lower number." In the case described, the calculated measure [Measures].[My Calculation] has the default solve_order=0. The visualtotal() has its default solve_order of −4096.

These solve order settings cause the VisualTotals function to process first. During the evaluation of the calculated measure [Measures].[My Calculation], the value of the contributing measure [Measures].[ActualSum] is retrieved first. The value is $29,004.00 in the cell for Quarter 1. That value is then filled into the remainder of the calculation: AGGREGATE(YTD() ,[Measures].[Actual_Sum] ) becomes AGGREGATE(YTD() , 29,004.00). Because the Time member in this row is an actual member, the YTD function resolves to a set of 1 tuple: {[TIME].[All TIME].[1993].[1]}. The final evaluation of the calculated member is therefore 29,004.00.

The calculated member expression for Quarter 2 resolves in the same way to AGGREGATE(YTD() , 30,815.00 ) But, this time, the YTD function resolves to a set with two tuples: {[TIME].[All TIME].[1993].[1], [TIME].[All TIME].[1993].[2]}. The AGGREGATE function creates the result of $61,630.00, which is twice the original value, and not necessarily expected.

For the other quarters, the results are similar, a multiple of the original measure, depending on the size of the resulting set returned by the YTD function.

Operating System and Release Information