Calculated Member and Measures in Excel 2013

Today blog post is brought to you by Alex McMains a Software Development Engineer in Test from the Office Business Intelligence team.

Besides many new, exciting features, Excel 2013 also offers enhancements to older features even those that may be a little more obscure to the everyday Excel user. Today we will shed some light on one of these features. The feature that I want to talk about here is the OLAP based Calculated Members and Measures.

Definition and Caveat
OLAP is an acronym for online analytical processing. Although there are technically different OLAPs, we refer specifically to Microsoft SQL Server Analysis Services data sources.

To be clear this excludes tabular model (e.g. PowerPivot) data sources. Please see creating DAX calculations to see how to get equivalent functionality with tabular model data sources.

What are OLAP based calculated members and measures?

The creation of OLAP based calculated members and measures was available as far back as Excel 2007. However, in order to use them effectively, it was necessary to write and maintain tedious macros. Excel 2013 provides a new drag/drop GUI to greatly simplify creation and management of all of your calculated members and measures.

Before we delve into the specifics of creating calculated members and measures, let’s briefly look at a very high level description of what they are and discuss why you might want them.

A calculated measure allows you to create your own client-side custom calculation that is not available on the server’s model. For example, the cube may provide a measure that shows a sales total for a region and another measure that shows the sales for each district in the region. Assume you want the percent of total for each district. Simply create a calculated measure using the total and individual districts measures to get the percentage and then use it in your pivot tables just as you would a regular cube measure.

A calculated member allows you to create a custom member in the cube hierarchy. For example, assume you have a hierarchy that shows the United States at one level and then each state at a level below it. Let’s assume that you are responsible for the Northwest region and this includes Oregon and Washington. In this case, you might create a calculated member called Northwest that always aggregates Oregon and Washington.

Connecting to an OLAP data source

Before we can create a calculated member or measure we need to connect to a Microsoft SQL Server Analysis Services data source. We will use the Adventure Works cube that comes with Microsoft SQL Server Analysis Services.

Let’s get started.

Step 1

Click on the Data tab in the ribbon and drop down the menu From Other Sources and select From Analysis Services.

STEP 2

Enter your server name then press next.

STEP 3

Now select the database Adventure Works DW and choose the cube Adventure Works. Then press Finish.

Step 4

Choose your visualization, either a PivotTable or a PivotChart and press OK.

OLAP Tools menu

If we now click on our pivottable or pivotchart we will see a new menu in the ribbon called PivotTable Tools. Click on the Analyze tab in this menu and we find an OLAP Tools menu. It is in this menu that we find the hooks that allows us to quickly and easily create and manage calculated members and measures.

Adding data to our pivot table or pivot chart

Let’s put some data on our PivotTable or PivotChart.

Step 1

In the field list on the right hand side of the worksheet, choose the measure Internet Gross Profit under the measure group Internet Sales.

Step 2

Scroll down to the member section to find Customer. Expand it to show Customer Geography and select it.

Step 3

Click on Country and then the down arrow to bring up the item selector.

Step 4

Ensure that only United States is selected and press OK.

Step 5

Expand United States on the pivot table so that it looks like this:

We are now ready to create custom calculations.

Creating a calculated measure

Go to the OLAP Tools menu once again under PivotTable Tools on the ribbon and click on MDX Calculated Measure…

Looking under the Internet Sales measures, it’s not necessarily clear what the Revenue is. Perhaps it’s Internet Sales Amount, perhaps not. We know that Profit = Revenue – Cost, and we have pretty obvious profit and cost numbers so let’s create a custom measure called Internet Revenue.

One thing to notice is that anything in the Fields and Items list can be dragged/dropped to the MDX box to help more easily create the calculation and prevent typos.

Once we press OK, we can find our calculation in the field list just as though the measure had been from the cube itself. The one difference is that our calculation can have a display folder name which we called Northwest Region Calculations. Go ahead and select the new measure and add it to your pivot table.

Congratulations! You have now created an OLAP based calculated measure. Now let’s look at creating a calculated member which is slightly more complex..

Creating a calculated member

A calculated member can be created from the same OLAP Tools menu as a calculated measure. We instead choose MDX Calculated Member… and get a slightly different dialog box. Before we create a calculated member, let’s discuss the dialog a little.

The Parent Member box in particular has a few not-so-obvious features that are worth acknowledging:

Now let’s create a simple calculated member.

Choose a name for your new calculated member, for example, Northwest Region. Next choose the parent member to be United States under Country which is under Customer Geography. Drag United States from the Fields and Items list to the Parent Member box.

Now drag Oregon to the MDX box. Type ‘+’ and then drag Washington. You should now see the following:

Click OK.

Congratulations! You have now created a calculated member. Verify this in your pivot table.

NOTE: If your Analysis Services version is older than 10.5 (2008 R2), you may not be able to deselect the calculated member from the item selector

Managing calculated members and measures

Having a GUI to create calculated members and measures is nice. What is even nicer is having a GUI to manage and edit them. Going back to the OLAP Tools menu we find that there is another option called Manage Calculations… From this dialog we can Create, Edit, or Delete a calculated member or measure.

NOTE: Once a calculated member is created, neither its parent member nor its parent hierarchy can be changed. Should you wish to change these, you will need to make another calculated member and copy your information over to it.

In closing

I hope you’ve enjoyed your tour through OLAP country. I’ve tried to provide something that can be used by anyone with even a basic familiarity of Microsoft SQL Server Analysis Services. To more advanced users, I hope you were able to gather some useful information from this posting as well.

Cheers.
Alex McMains, Office BI”

About Advanced Network Consulting:

ANC is a full service computer consulting company in Los Angeles and Orange County providing cabling, office moves, network security and connectivity, server installs and upgrades.  One highlight of our business is meeting with business owners to plan, strategize, and implement an infrastructure for a new venture.  With our many connections, establishing a new business is as simple as a phone call. We coordinate with the contractor, electricians, phones service provider, printer, web developer and host, and any other vendors involved when establishing a business.  We’ll take you from an empty shell of a building to a secure network and fully functioning office.  For a complimentary design meeting, call 562.903.3992.  Let’s get your business fully operational.