Business Intelligence Blog

Menu

Creating Custom Calculations in Excel with Pivot Table Extensions

One of the biggest hurdles that users are faced with when using Pivot Tables is that all measures and dimensions must be defined within the cube. Having the defined data available is really convenient for drag and drop analysis however in most situations, the users requirements can quickly outgrow the bounds of the definition of the cube. I want to see such and such in my data is the usual type of requirement. Sure, developers and report writers can create calculations but what about users – the people that need to use the data? This post looks at OLAP PivotTable Extensions, a tool which increases the usability of pivot tables for end users.

Traditional PivotTables just don’t give the user enough flexibility. Sure Excel 2013 has some nice features for creating calculations in Pivots but I haven’t worked with any corporate client that is seriously considering using 2013 just yet. There is just too much corporate tape to cut through. So let me just say, if your users aren’t using this tool, they should be. You may argue that users could use Power Pivot but, that really defeats the purpose of having a cube doesn’t it? Besides, why should the users have to handle data just to do some simple analysis? Besides that, the addin is free, talk about a lower barrier to entry for self-service BI!

This post looks at creating calculations using the OLAP pivot table extensions addin.

A Quick Overview

Once the add-in has been installed it can be accessed from a pivot table by simply right clicking on that pivot table. For example, consider the pivot table below which shows the [sales amount] and [sales quota] by year in adventure works. To add a custom calculation to this pivot, all I need to do is right click and select “OLAP Pivot Table Extnesions …”

This opens the following window (note that the calculations tab is selected);

Calculations On Measures

The first example that we can use for creating a calculation involves creating a new measure that is based on other measures. For example based on the [Sales Amount] and [Sales Amount Quota], we might like to see what the variance is. We can express this as [Sales Amount] – [Sales Amount Quota] but we should remember two things;

Firstly, there are spaces in in the names so we need to surround the name with square bracket (ie [, ]).

Secondly, the pivot is based on MDX. This is a decorative language and has its own rules. While these rules can sometimes be bypassed (as we will see below), it’s probably the better idea to use the fully qualified name for items that are referring to.

If we consider the requirement for sales variance (lets call it [sales variance]) we could simply create the measure using the following formula [Sales Amount] – [Sales Amount Quota]. This is shown below as;

Once the measure has been defined I can added to the pivot table by clicking the ‘Add to Pivot Table’ button.

Fully Qualified Names

I stated above that MDX has requirements for how we refer to items in the language, and it is a good idea to use the fully qualified name. An exception to this is the requirement to use the fully qualified names for measures. For example if we are in cell C8 and we create a formula that references C5, we can see that Excel adds its own calculation (one that users the formula GETPIVOTDATA).

By examining this formula, we can see that the fully qualified name for the [Sales Amount] measure is [Meaures].[Sales Amount]. Also note that the fully qualified name for [CY 2008] is [Date].[Calendar Year].&[2008].

The use of the GETPIVOTDATA function is a very useful trick that you can use to determine fully qualified name for pivot cells.

Using Static References

One of the restrictions of pivot tables is that it’s very hard to reference individual cells in the pivot. For example, if we wished to show the sales amount for 2005 in the pivot we would generally filter columns or rows to 2005 and add the [sales amount] measure. We can use the pivot table extensions with a static reference to include the 2005 sales amount value as a column in the pivot. All we have to do is to create a new measure for the cell coordinates that we are interested in.

Consider the GETPIVOTDATA formula for the sales amount in 2005, that is;

We can simply create a measure that references this coordinate. This is ( [Sales Amount] , [Date].[Calendar Year].&[2005] )

Note that coordinates should be enclosed in parenthesis and separated by commas.

A really cool thing about creating this new measure is that we can use it by other measures. So we could show the variance compared to 2005 (Sales Amount Measure) by creating a measure that refers to the [Sales Amount 2005] measure. This measure would be;[Sales Amount] – [Sales Amount 2005]

Note, that we did not have to keep ‘referenced’ measures in the pivot table to use their outcome. For example we can remove the measure [Sales Amount 2005] from the pivot table and see the measure [Sales Variance 2005] in the pivot. Measures are removed just as any pivot measure, by dragging them from the ‘Values’ box in the field selection list.

The use of the word static to this section implies that we will only reference data from 2005 in our calculated measure. More often than not we want to refer to a coordinate in the context of another. This is usually the ‘next year’ or the ‘prior year’. For example we might like to see the [Sales Amount] variance compared to last year. In order to achieve this we will create a measure [Sales Amount LY] and [Sales Variance LY].

Calculations using Relative References

If we recall the GETPIVOTDATA formula for the sales amount in 2005, we can also discover that the ‘2005’ amount relates to a specific dimension, and the hierarchy within that dimension. We can see this in the highlighted sections below;

These both give us the same information, specifically that the hierarchy [Date].[Calendar Year] is being used. Additionally, we should know that each row actually refers to a specific member (for example 2005). If we want to create a measure that uses a relative reference we have two to use the hierarchy name and the relative function.

Arguably the .prevmember function is one of the most useful relative functions available. This function takes the current coordinate (item) and substitutes the prior one for it. Therefore, if we are on the 2006 row, the .prevmember would give us the 2005 coordinate. Also note that because we are using a relative function, the ‘exact’ coordinate is not referred to, rather the hierarchy is used in formulas.

Therefore, we can get the prior year of a row using the following coordinate [Date].[Calendar Year].prevmember

This may seem a little bit complicated at first, and it may help to think that the rows shown are from the [Date].[Calendar Year] hierarchy, rather than specific values. For example [Date].[Calendar Year].&[2005].

If we wish to create a calculated measure to show what sells amount was last year we simply need to use the formula

ConclusionThe use of the OLAP PivotTable Extensions offers a huge improvement to the usability of pivot tables. This post is looked at the ability to create custom calculations within the pivot so that the user can create their own analysis.

Inspired by your post, I further explored possibilities. I realized that the addin can also be used to created calculated members.
I needed to create a report for the completed month of the current year. I started by creating a set (an XL2010 feature), but you can only place the set in the column or row area of the pivot table. This has the effect of displaying each of the set members on a row/column. I only wanted the total result and hide the details.