This project has moved. For the latest updates, please go here.

Calculation across multiple dimensions.

Topics: Resolved
Jun 11, 2009 at 10:13 AM
Edited Jun 11, 2009 at 10:15 AM

I have the following pivot table built using an OLAP cube.  What I would like to do is create a calculation comparing the COST in ACTUALS against the COST in BUDGET.

 

I have no idea how I should reference the 2 numbers.  Sorry but I am fairly new to MDX and writing queries - any help would be greatly appreciated.

The MDX for the current query is :

 

SELECT NON EMPTY Hierarchize(AddCalculatedMembers({DrilldownLevel({[CUBE].[Version].[All]})})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS , NON EMPTY Hierarchize(AddCalculatedMembers({DrilldownLevel({[CUBE].[Manufacturing Site].[All]})})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS  FROM (SELECT ({[CUBE].[Version].&[BUDBUDG], [CUBE].[Version].&[ACTUALS]}) ON COLUMNS  FROM [PFFORECAST]) WHERE ([CUBE].[Year].&[2010],[Measures].[FSP]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

 

 

Thanks

Alan

 

 

Year 2001             COST Column Labels     Row Labels ACTUALS BUDGET Grand Total XXX 10133555.6 58393429 68526984.6 YYY 6326932.97 34969381 41296313.97 ZZZ 8300370.36 29775856 38076226.36 AAA 3813234.59 20941167 24754401.59 Grand Total 28574093.52 144079833 172653926.5

 

Coordinator
Jun 11, 2009 at 11:18 AM

Would the following work?

Calculation Name: [CUBE].[Version].[All].[Variance]
Formula: [CUBE].[Version].&[ACTUALS] - [CUBE].[Version].&[BUDBUDG]

I would also study this page:
http://olappivottableextend.codeplex.com/Wiki/View.aspx?title=Calculations%20Help

 

Marked as answer by furmangg on 6/10/2014 at 3:50 PM
Jun 11, 2009 at 12:39 PM

Thankyou very much - yes that has worked great!!

 

I did have the formula correct at one stage but the Calculation Name was just called Test.  I guess there is a lot to learn!!

 

Great piece of software.

 

 

Regards

Alan