MDX Balance to Totals

Question

In the example below I have a products dimension with product id 1,2,3,4..etc. I want to create a calculated member called Balance To Totals that will sum over all the products they have chosen and subtract it from the grand total. This should happen over any measure they select. In the example below I have the products dimension on rows, and two measures on columns. If the user selects product 1,4,5 then the balance to totals will be the sum of those three products subtracted from the grand totals. Does anybody know how I can accomplish this through a calculated member?

Product Id      Cost($)      Profit($)
1                80             3
4                70             4
5                50             2
BalToTotal       125            25

Grand Total      325            34

michele michele · Accepted Answer · 2013-03-05T08:58:58

I just set up a simple MDX query on AdventureWorks Cube, so you can play with it:

with set [SelectedProducts] as {[Product].[Subcategory].&[26], [Product].[Subcategory].&[1]}
     member [Product].[Subcategory].[BalToTotal] as [Product].[Subcategory].defaultmember - Aggregate ([SelectedProducts])
select
{[Measures].[Internet Sales Amount], [Measures].[Reseller Order Quantity]} on 0,
{[SelectedProducts], [Product].[Subcategory].[BalToTotal], [Product].[Subcategory].defaultmember}  on 1
from [Adventure Works]

The output will be:

                 Internet Sales Amount     Reseller Order Quantity
Bike Racks        €39.360,00               2.838
Mountain Bikes    €9.952.759,56            23.351
BalToTotal        €19.366.557,66           188.189
All Products      €29.358.677,22           214.378

Hope this help

MDX Balance to Totals

1 Answers