Jeremy Kashel

Jeremy Kashel's Blog

Adding a Calculated Field to an Excel Pivot Table With Analysis Services

One question that I get asked from time to time is how to add a calculated field to an Excel Pivot Table that’s connected to an Analysis Services cube. I’ve been aware of a workaround to do this for a while, but a quick Bing :-) revealed that the common answer given to this question is that it’s not possible.

It definitely is possible, it’s just not that obvious and is perhaps a little limited. Here’s how it can be done in Excel 2010:

Sample Cube

First of all, connect to a cube and produce a pivot table report. I’ve connected to the 2008 R2 version of the Adventure Works DW cube and have produced the following report, using the Internet Sales Amount measure:

image

Adding a Calculated Field

If you now open the Field List, you will see that Internet Sales Amount is selected of course. Here’s the trick – you can select the measure twice by dragging it into the Values area, which will give the following:

image

Now click the drop down next to Internet Sales Amount2, which will display the following window:

image

You can change the name of the field – I’ve called mine Percent Share. In the Show Values as drop-down, I’ve picked % of Grand Total, so that I can see the share of the sales in each country across all years. So now the report is as follows:

image

Summary

A better approach is to put as many calculations in the cube as possible, but as some calculations requested by users are report-specific, this feature of Pivot Tables allows users to produce the reports that they want.

The calculations that you can carry out are only those in the ‘Show values as’ drop-down. There’s quite a few in there, especially in Excel 2010, as mentioned here. If that’s not enough, then an alternative is to use the OLAP Pivot Table Extensions, which is available to download on Codeplex.

Comments (2) -

  • Anonymous

    2/7/2012 2:56:03 PM | Reply

    Hello, I tried to use your approach.  HOwever, in the cube, I could not drag the same field twice to the Values area.  Any thoughts?


  • Jeremy Kashel

    2/10/2012 3:51:59 PM | Reply

    It may be version specific - I'm using Office Professional Plus 2010, version 14.0.6112.5000 (64 Bit). I don't have Office 2007 to hand, so not sure if it would work with 2007, for example. This does have to be done in the PivotTable Field List, so as long as you're doing the dragging and dropping in that window, then it should work fine.

Loading