PowerPivot Window: Advanced Tab

Looking for help with Power Pivot in Excel 2013? Go to Power Pivot Help on Office.com.

This section provides Help on the Advanced tab in the PowerPivot window, including the dialog boxes that are available from this tab. For an overview of the user interface and instructions on how to open the PowerPivot window, see Take a Tour of the PowerPivot UI. The Advanced tab enables you to create or edit perspectives, summarize a numeric column by an aggregation function, and set reporting properties for a reporting client tool, such as Power View.

Advanced tab in PowerPivot window


In Windows Vista and Windows 7, features in the PowerPivot window are available on a ribbon, which is discussed in this section. In Windows XP, features are available from a set of menus. If you are using Windows XP and want to see how the menu commands relate to the ribbon commands, see The PowerPivot UI in Windows XP.

To display the Advanced tab

  1. Click the File button to the left of the Home tab. The File menu opens.

  2. Click Switch to Advanced Mode. The Advanced tab appears to the right of the Design tab.


Perspectives are metadata layers that track different slices or sets of data. Perspectives are typically defined for a particular user group or business scenario and make it easier to navigate large data sets. You can click Perspectives on the Advanced tab to open the Perspectives dialog box. For more information about Perspectives, see Perspectives in PowerPivot, Perspectives Dialog Box, and Create a Perspective (Tutorial).

Show Implicit Measures

Implicit measures are those that you create by dragging a field to the Values list of the Field List in Excel. This is in contrast with explicit measures, which you create as a standalone custom calculation in the workbook and can be reused by multiple PivotTables or PivotCharts in the same workbook. Implicit measures are easy to create and use, but do not support the same level of functionality as explicit measures. For example, you cannot create a KPI based on an implicit measure, nor can you use an implicit measure in a Power View report.

By default, an implicit measure does not appear in the Calculation Area. If you want to show these measures in the Calculation Area, click Show Implicit Measures. An implicit measure will appear in the same table and under the same column upon which it is based.

Summarize By

Select a numeric column to summarize its values by an aggregation function. Selecting a function from the drop-down list sets the default aggregation behavior for the Field List and reporting client tools, such as Power View.

Reporting Properties

In the Reporting Properties area of the Advanced tab, you can set properties that affect report design in reporting client tools, such as Power View.

Table Detail opens the Table Detail dialog where you can specify which fields are automatically included in a report whenever the table is used. Choosing the fields, and the order in which they appear, eliminates redundant steps for report authors because once on a table adds both table and fields to the report. For more information, see Configure Default Field Set for Power View Reports.

Table Behavior opens the Table Behavior dialog where you can change the default grouping behavior for the table, and set default labels and images used in card and chart report layouts. For more information, see Table Behavior Dialog Box.

Image URL is a property that you can set on a column that contains a URL to an image on a SharePoint site or on the web. Setting the property instructs Power View to retrieve the image file rather than return the URL address as text data in the report.

See Also


Create a Perspective (Tutorial)


Perspectives Dialog Box


Take a Tour of the PowerPivot UI

Perspectives in PowerPivot

Create a Reporting Services Report Using PowerPivot Data