Answered by:
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]
)
ENDThis 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 subselects 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
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 
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.
 Edited by Minnie Shi  Schurr Wednesday, September 19, 2012 4:13 AM
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
 Proposed as answer by Minnie Shi  Schurr Thursday, September 20, 2012 12:50 AM
Wednesday, September 19, 2012 7:07 PM 
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