MDX: Grand Total / Sub Total

One of the most common issues faced in mdx is grand total or sub total not coming properly when some arithmetic operations like measure1 [* or + or - or /]  measure2 is used


The various ways that one may think of while trying to resolve this are
1. scope
2. case statements
3. measure expression
4. named calculation in dsv / view
5. have the etl populate the data in required way
6. Visual Totals


the order listed is generally the preference taken to resolve the issue. the problem statement can further be  categorized by the usage of attribute hierarchy versus natural hierarchy in the row axis and whether changes to be done on the measure itself or to be done on the calculated measure.


let me build a geography dimension for this and revenue fact. The geography dimension will have a country-state-city hierarchy. The revenue will have revenue, tax percent, tax revenue measures.

CREATE TABLE [dbo].[Geography](             [CityId] [int] IDENTITY(1,1) NOT NULL,             [City] [varchar](50) NOT NULL,             [State] [varchar](10) NULL,             [StateCode] [varchar](50) NULL,             [CountryCode] [varchar](50) NULL,             [Country] [varchar](50) NULL,  CONSTRAINT [PK_Country] PRIMARY KEY CLUSTERED (             [CityId] ASC )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] ) ON [PRIMARY] CREATE TABLE [dbo].[Revenue](             [id] [bigint] IDENTITY(1,1) NOT NULL,             [CityId] [int] NULL,             [Tax] [float] NULL,             [Revenue] [money] NULL,             [TaxRevenue] [money] NULL,             [IsTaxConsidered] [bit] NULL,  CONSTRAINT [PK_Revenue] PRIMARY KEY CLUSTERED (             [id] ASC )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] ) ON [PRIMARY]

In the cube, create a geography dimension and set the attribute relationship as follows as
City Id->City->State->Country, State->State Code, Country->Country Code and create a hierarchy Country->State->City. Hide the attributes City Id, Country Code and State Code.
Create a cube with Tax, Revenue and Tax Revenue measure with Aggregation type as SUM and relate the measure group and dimension with relationship type as Regular and key as City Id.


Now i would like to have a calculated measure having the formula: TaxRevenue= Revenue * Tax Revenue.

CREATE MEMBER CURRENTCUBE.[Measures].[cTaxRevenue] AS [Measures].[Revenue]*[Measures].[Tax], VISIBLE = 1  ;

Result

Now if one see, clearly the grand total value is not proper. What it does multiplies the aggregated value. Expected value is 24. On the row axis, the city attribute hierarchy is used.
Scope

CREATE MEMBER CURRENTCUBE.[Measures].[cTaxRevenue] AS null, VISIBLE = 1  ;  SCOPE([Measures].[cTaxRevenue],[Geography].[City].members); --Includes All + other members This = case when [Geography].[City].currentmember.level.ordinal>0 then     [Measures].[Revenue]*[Measures].[Tax] else    sum([Geography].[City].[City].members, [Measures].[Revenue]*[Measures].[Tax]) --All member is not included end ; END SCOPE;

Result

if one includes All members, the result would be wrong, example: Geography.City.members were used. At this one applies filter by excluding Dallas, then still the grand total would be reading as 24 and not 16. Let us address this later. Now how about if one drops in Hierarchy on row axis.
Result

SCOPE([Measures].[cTaxRevenueH],[Geography].[GeoHier].members); This =sum(         Descendants([Geography].[GeoHier].CurrentMember,,LEAVES),         [Measures].[Revenue]*[Measures].[Tax]); END SCOPE;

This query works for both the attribute hierarchy as well as the geography hierarchy.

So all works well at this point of time. Now apply filter in by selecting City not in Bangalore. What you see now is that the total comes as 11 and not 8 though the data for Bangalore is filtered out.


To solve this problem, one could directly create a named calculation in Revenue fact in the DSV with expression as Revenue * Tax and then process the cube without any scope statements. Now applying filter should give you appropriate results. The filter is not honored by the scope statements. 


if one wants to make it generic, then we could change it to 

SCOPE([Measures].[cTaxRevenue]); This =sum(         EXISTING Descendants(Axis(0).Item(0).Item(0).Hierarchy.CurrentMember,,LEAVES),         [Measures].[Revenue]*[Measures].[Tax]); END SCOPE;

if this has to work with mdx in query analyzer, then Axis(0) need to be Axis(1). 
To work it both in browser and mdx query, change the query by adding a member like this

CREATE MEMBER CURRENTCUBE.[Measures].[AxisNo] as  case     when  IsError(Extract( Axis(0), Measures).Count) then 0         when  IsError(Extract( Axis(1), Measures ).Count) then 1         else -1     end; SCOPE([Measures].[cTaxRevenue]);  This =sum(        Descendants(Axis([Measures].[AxisNo]).Item(0).Item(0).Hierarchy.CurrentMember,,LEAVES),         [Measures].[Revenue]*[Measures].[Tax]);  END SCOPE;

Further to this if one wants to select certain cities in the Mdx, then apply Visual Totals for the totals to come properly as scope would not honor filters.

with member [Measures].[cTaxRevenueFilterApplied]  as             case when  [Geography].[City].currentmember is [Geography].[City].[All] then              sum(except(VisualTotals(axis(1)),[Geography].[City].[All]), [Measures].[Revenue]*[Measures].[Tax])              else               [Measures].[Revenue]*[Measures].[Tax]              end     select {[Measures].[Tax],[Measures].[Revenue],[Measures].[cTaxRevenue], [Measures].[cTaxRevenueFilterApplied] } on 0, {[Geography].[City].[All],[Geography].[City].&[Austin],[Geography].[City].&[Bangalore]} on 1 from [Cube]

Links
http://sqlblog.com/blogs/mosha/archive/2007/09/26/how-to-detect-subselect-inside-mdx-calculations-aka-multiselect-in-excel-2007.aspx