CodePlexProject Hosting for Open Source Software

This page is a resource for the OLAP PivotTable Extensions if you are creating private PivotTable calculations and need help. The formula used is a standard Analysis Services MDX expression. All formulas are executed on the Analysis Services server.

The best place to ask for help with MDX expressions is at the MDX tag on StackOverflow or the Microsoft Analysis Services Forums.

IIf( [Measures].[Internet Order Count] = 0 ,null ,[Measures].[Internet Tax Amount] / [Measures].[Internet Order Count] )

- MDX Function Reference
- MDX Function Usage Examples
- VBA functions you can use in MDX expressions.
- If Excel 2003 or later is installed on the Analysis Services server, these Excel functions can be used in your MDX expressions.

As an example, you could define the following calculation:

- Calculation Name: [Product].[Product Categories].[Bikes].[Road Bikes + 25%]
- Formula: [Product].[Product Categories].[Bikes].[Road Bikes] * 1.25

These dimension calculated members then show up in the PivotTable just like normal dimension members:

For more information on NON_EMPTY_BEHAVIOR in Analysis Services 2005 see the Performance Guide and search for "NEB". Do not include a NON_EMPTY_BEHAVIOR if your PivotTable is running off an Analysis Services 2008 cube.

You can also set the FORMAT_STRING, BACK_COLOR, and FORE_COLOR properties (though since you can format the data in the PivotTable with native Excel functionality, this isn't strictly necessary). You may also set the SOLVE_ORDER property for situations when multiple calculations intersect on a particular cell.

The following is an example of setting such properties:

[Measures].[Internet Tax Amount] / [Measures].[Internet Order Count] ,NON_EMPTY_BEHAVIOR = [Measures].[Internet Tax Amount] ,FORMAT_STRING = "$#,##0"

[Measures].[Internet Sales Amount] * 2 ,DISPLAY_FOLDER = 'My Folder'

Or you could put it in the folder for a measure group:

[Measures].[Internet Sales Amount] * 2 ,ASSOCIATED_MEASURE_GROUP = 'Internet Sales'

Or you could put it in a subfolder inside a particular measure group:

[Measures].[Internet Sales Amount] * 2 ,DISPLAY_FOLDER = 'My Folder' ,ASSOCIATED_MEASURE_GROUP = 'Internet Sales'

'[Measures].[Internet Tax Amount] / [Measures].[Internet Order Count]'

That being said, using OLAP PivotTable Extensions to add calculations to a PowerPivot PivotTable works fine most of the time. It lets you do things which are not possible with PowerPivot itself, for example, adding calculated members to dimensions (so that the calculation applys to every measure).

Note the Solve_Order workaround mentioned in the following discussion # VALUE when using AGGREGATE() against PowerPivot. Also note the tips on troubleshooting DAX errors in PowerPivot mentioned in that discussion. Finally, note that the calculations you can add in OLAP PivotTable Extensions are MDX calculations, not DAX calculations.

Unfortunately, PowerPivot for Excel 2013, which comes installed with Excel, but has to be activated under COM Add-ins, has removed the ability add MDX calculations to PivotTables. For more about Excel 2013 and the Excel Data Model, see the Excel 2013 page.

Last edited Sep 20 at 2:11 PM by furmangg, version 17