Microsoft Excel 2013 offers a variety of business intelligence capabilities that enable you to create powerful reports, scorecards, and dashboards. New and improved capabilities include the ability to create calculated items, such as Calculated Measures, Calculated Members, and Calculated Fields. Read this article to learn about calculated items and whether they’re supported in Excel Services.
Calculated items in Excel Services
In Excel, people can create calculated items that include Calculated Measures, Calculated Members, and Calculated Fields. Calculated items enable you to define and use custom calculations and sets of items that do not exist in the databases that are used to create PivotChart reports or PivotTable reports.
When you have a workbook that contains calculated items, you can share the workbook with others by uploading it to a SharePoint library. Depending on how your SharePoint environment is configured, people can typically view and use workbooks that contain calculated items in a browser window. However, some environments might not support that capability.
If your organization is using Office Web Apps Server alongside SharePoint Server 2013 (on premises), then either Excel Services (SharePoint Server 2013) or Excel Web App (Office Web Apps Server)is used to render workbooks in a browser window. This decision can affect whether workbooks that contain Calculated Fields (created by using Power Pivot for Excel) can be viewed in a browser window.
The following table summarizes whether calculated items are supported in Excel Services (SharePoint Server 2013), Excel Web App (Office Web Apps Server), and Excel for the web (in SharePoint).
|
Calculated Item |
Excel Services (SharePoint Server 2013, on premises) |
Excel Web App (Office Web Apps, on premises) |
Excel for the web (in SharePoint Online) |
|---|---|---|---|
|
Yes |
Yes |
Yes, if the data sources that are used are supported in SharePoint. See Use external data in workbooks in SharePoint. |
|
|
Yes |
Yes |
Yes, if the data sources that are used are supported in SharePoint. See Use external data in workbooks in SharePoint. |
|
|
Yes |
No. Power Pivot capabilities, including Calculated Fields and Data Models, are not supported in Office Web Apps Server (on premises). |
Yes, if the data sources that are used are supported in SharePoint. See Use external data in workbooks in SharePoint. |
For more information, see the following resources:
-
Comparing Excel Services in SharePoint to Excel Web App
-
Business intelligence in Excel and Excel Services (SharePoint Server) (this applies to on-premises environments)
-
BI capabilities in Power BI for Microsoft 365, Excel, and SharePoint (this applies to Microsoft 365 environments)
Important:Â If you attempt to view a workbook that contains calculated items (or unsupported features) in a browser window, and you receive an error message that indicates the workbook is not supported, try opening the workbook in Excel 2013.
What are Calculated Measures?
A Calculated Measure is a custom calculation that you can create in Excel when you are working with multidimensional data that is stored in SQL Server Analysis Services. Calculated Measures are useful for defining calculations that might not already exist in a database. Examples of custom calculations can include any of following:
-
A sales quota measure that uses a particular formula
-
A percent-of-total measure for items in a group
-
A gross profit measure that uses a complex query
-
A revenue measure that uses the sum of gross profit and product cost
When you create a Calculated Measure, you define a Multidimensional Expressions (MDX) query. This is easily done using a Calculated Measure dialog box in Excel, which enables you to use drag-and-drop functionality to set up your query.
To create a Calculated Measure in Excel
-
Create a PivotTable report or a PivotChart report using data stored in an Analysis Services cube.
-
On the Analyze tab, in the Calculations group, choose OLAP Tools > MDX Calculated Measure. The New Calculated Measure dialog box opens.
-
In the Name box, specify a name for the Calculated Measure.
-
(This step is optional.) To specify where the Calculated Measure should appear in the PivotTable Fields (or PivotChart Fields) list, take one or both of the following steps:
-
Use the Measure Group list to specify where you want the Calculated Measure to appear in the PivotTable Fields (or PivotChart Fields) list. If you do not specify a Measure Group, the Calculated Measure will appear in a group called Values.
-
In the Folder box, type a name to create a display folder for the Calculated Measure.
-
-
From the Fields and Items tab, drag an item (such as a measure) to the MDX pane.
-
Add an operation, such as +, -, /, or * after the item in the MDX pane.
-
From the Fields and Items tab, drag another item to the MDX pane.
-
Repeat steps 5-7 until you have the items and your formula set up. For example, if you are creating a Calculated Measure called Revenue, in the MDX pane, you might have a query that resembles[Measures].[Product Cost]+[Measures].[Gross Profit]
-
Choose Test MDX to make sure the query will work correctly.
-
Choose OK to create the Calculated Measure.
-
To use the Calculated Measure in the report, select it in the PivotTable Fields (or PivotChart Fields) list. You’ll find it in the location that was specified in step 4.
Note:Â Because the Excel MDX Calculated Measure is used to create a session specific calculation for a SQL Server Analysis Services data source, your Calculated Measure will be limited to the session and the data source connection you used. Learn more about creating session-scoped Calculated Members.
What are Calculated Members?
A Calculated Member is a set of members that you can define in Excel when you are working with multidimensional data that is stored in Server Analysis Services. Calculated Members are useful for defining sets of items that might not already exist in a database. Examples of these custom sets can include any of following:
-
An area that consists of certain geographical areas, such as countries, regions, or states
-
A group of products that count towards a sales representative’s quota amount
-
A set of promotional activities related to a particular marketing campaign
Similar to Calculated Measures, when you create a Calculated Member, you define an MDX query. This is easily done using a Calculated Member dialog box in Excel, which enables you to use drag-and-drop functionality to set up your query.
Note: When you work with a PivotTable in Excel and you use the OLAP tools to add a calculated member, you won’t be able to deselect it in the Field List's dropdown list if your data source is connected to a server running SQL Server 2008 or earlier. If the data source is connected to a server running SQL Server 2008 R2 or later, you can select and deselect the calculated member in the filter dropdown list.
To create a Calculated Member in Excel
-
Create a PivotTable report or a PivotChart report using data stored in an Analysis Services cube.
-
On the Analyze tab, in the Calculations group, choose OLAP Tools > MDX Calculated Member. The New Calculated Member dialog box opens.
-
In the Name box, specify a name for the Calculated Member.
-
Use the Parent Hierarchy list to specify where you want the Calculated Member to appear in the PivotTable Fields (or PivotChart Fields) list. Pay close attention to what you select. You will need to know where you have specified the Calculated Member in order to use it in the PivotTable report (or PivotChart report).
-
From the Fields and Items tab, drag an item (such as a dimension hierarchy) to the MDX pane.
-
Add an operation, such as +, -, /, or * after the item in the MDX pane.
-
From the Fields and Items tab, drag another item to the MDX pane.
-
Repeat steps 5-7 until you have the items and your formula set up. For example, if you are creating a Calculated Member called Core Products that includes all but two product categories, in the MDX pane, you might have a query that resembles[Product].[Product Categories]-[Product].[Product Categories].[Category].&[4]-[Product].[Product Categories].[Category].&[3]
-
Choose Test MDX to make sure the query will work correctly.
-
Choose OK to create the Calculated Member.
-
To add the Calculated Member to the PivotTable report (or PivotChart report), take the following steps:
-
Make sure that at least one measure is selected for the report.
-
In the PivotTable Fields (or PivotChart Fields) list, expand the parent dimension that you specified in step 4.
-
Select the check box next to dimension hierarchy that corresponds to the hierarchy that you used to create the Calculated Member. The report displays information for all the dimension members in the group, including the Calculated Member that you created.
-
-
(This is optional.) To display information for only the Calculated Member in the report, take the following steps:
-
In the PivotTable Fields (or PivotChart Fields) list, point to the dimension hierarchy that contains the Calculated Member.
-
When the down arrow appears, click (or tap) it to open the Select field dialog box.
-
Clear the checkboxes for all the items, except for the Calculated Member that you created.
-
What are Calculated Fields?
Calculated Fields are useful when you want to create a calculated item in a PivotTable or a report that does not use multidimensional data stored in Analysis Services, but instead uses data in an in-workbook Data Model created by using Power Pivot in Excel. The values in calculated fields can change based on context. The context is determined by selections on rows, columns, filters, or in a custom formula using Data Analysis Expressions (DAX) in PowerPivot.
Similar to Calculated Measures and Calculated Members, Calculated Fields are listed in the PivotTable Fields (or PivotChart Fields) list, typically in the Values group. You have several options available for creating a Calculated Field.
To create a Calculated Field in Excel
For detailed information on how to create and work with Calculated Fields, see the following resources:
