CODEWITHSUNDEEP
sql-serverssasmdx
JJ0022
JJ0022

Reputation: 33

MDX wrong sum on grand total in excel

My SSAS cube uses currency conversion intelligence which will show some measures calculated from currency rates. However, in Date hierarchy dimension, I need to add more MDX to make my own Date hierarchy to show correct summation as follows.

> // Scope for sum [Calendar year - month - date].[Month] SCOPE
> ([Date].[Calendar year - month - date].[Month].MEMBERS,
> {[Measures].[Revenue],  Measures.[Sales]});
>     THIS = Sum( EXISTING [Date].[Calendar year - month - date].CurrentMember.Children ,[Measures].CurrentMember); END SCOPE;
> 
> // Scope for sum [Calendar year - month - date].[Calendar year] SCOPE
> ([Date].[ Calendar year - month - date].[Calendar year].MEMBERS, 
> {[Measures].[Revenue],  Measures.[Sales]});
>     THIS = Sum( EXISTING [Date].[Calendar year - month - date].CurrentMember.Children ,[Measures].CurrentMember); END SCOPE;

This works just fine on Month and Calendar Year level. However, when looking at the grand total in Excel, it always shows sum of all years. When I change the filter to select only 2 years, it still shows sum all years in grand totals.

Upvotes: 0

Views: 2140

Answers (1)

Danylo Korostil
Danylo Korostil

Reputation: 1484

No MDX needed. You have to toggle VisualTotals checkbox in Excel: http://cloud.addictivetips.com/wp-content/uploads/2009/11/PivotTableVisualStyle.jpg

Upvotes: 0

Related Questions