Create a Perspective (Tutorial)
Looking for help with Power Pivot in Excel 2013? Go to Power Pivot Help on Office.com.
In this lesson you will use PowerPivot to create and use a perspective as the basis for a store sales report. Perspectives are subsets of tables and columns from the model that track different sets of data. Perspectives are typically defined for a particular user group or business scenario (such as for a sales team) and make it easier to navigate large data sets. For more information about perspectives, see Perspectives in PowerPivot.
Prerequisites
This topic is part of a PowerPivot for Excel tutorial, which should be completed in order. For information about prerequisites and the first step in the tutorial, see PowerPivot for Excel Tutorial Introduction.
Create a Perspective
To add a perspective
In the PowerPivot window, make sure you are in Advanced Mode (you can see the Advanced tab). If you cannot see the tab, click the File button to the left of the Home tab, and then click Switch to Advanced Mode.
On the Advanced tab, click Perspectives. The Perspectives dialog box appears.
To add a new perspective, click New Perspective.
If you create an empty perspective with all of the field object fields, then a user using this perspective will see an empty Field List. Perspectives should contain at least one table and column in order to be useful.
Type Sales Perspective as the name for the new perspective. The name is a required field.
Select StoreName from the Stores table to include it in the perspective.
Select the Categories hierarchy from the DimProduct table.
Select CalendarYear from the DimDate table.
Select ContinentName from the Geography table.
Click the expand button to the left of the FactSales table to see the individual columns the table, and then select the following columns: StoreSales, StoreSalesPrevYr, and YOYGrowth.
Click OK to add the new perspective and close the Perspectives dialog box.
To rename the perspective, double click the column header (the name of the perspective) or click the Rename button, and then change the name to Sales Report.
Use the Perspective in a PivotTable Report
In this step, you will use the perspective you just created to build a PivotTable report. You will quickly notice how much simpler it is to create a report when you use a perspective that excludes the tables and fields not relevant to the analysis.
To create the report
In the PowerPivot window, on the PowerPivot Home tab, click PivotTable.
Select New Worksheet.
Select the empty PivotTable.
If you get an error message telling you the data list is no longer valid, right-click the table and select Refresh Data.
In the PowerPivot Field List, at the top of the list, click the down arrow next to [Default Perspective] and select Sales Report.
In the Stores table, select the StoreName field. Ensure this field displays in the Row Labels window of the Field List.
In the DimDate table, select the CalendarYear field. Ensure this field displays in the Column Labels window of the Field List.
Filter the CalendarYear field so that only data from 2008 and 2009 is used in the PivotTable.
In the PivotTable, click the Filter icon by Column Labels.
Clear Select All, and then select 2008 and 2009.
In the FactSales table, select StoreSales, StoreSalesPrevYr, and YOYGrowth. Beneath YOYGrowth, verify that both Value and Status are selected.
In the Geography table, drag ContinentName to the Slicers Vertical area.
One at a time, click Asia, Europe, and North America to view the annual store sales metrics for each continent.
Use the Slicers and KPIs to Analyze your PowerPivot Data
At Contoso we want to evaluate annual store sales by territories. Based on what we discover, we may review marketing budgets and/or close stores to improve the numbers.
In the ContinentName Slicer, select Asia. The KPIs provides a visual indicator that lets us quickly identify which stores are below target.
Click North America to reveal the declining trends in that market. As we can see from the KPIs, there appear to be market differences that go beyond individual store performance, with widespread decline for the majority of stores.
To further analyze the trends, let’s add the Categories hierarchy to the analysis. Expand DimProduct and drag Categories to the Rows area.
Adding Product Categories shows us that for many stores in North America, Audio is above target, while other categories are consistently below target. To what can we attribute this pattern?
For Europe, the KPIs show us a different pattern, with specific stores exceeding or failing in all categories. Further investigation will tell us whether we need to close individual stores for that region, or adopt the selling strategies of the successful stores more broadly across the channel.
Next Step
Congratulations! The tutorial is complete. You should now have a basic understanding of what you can do with PowerPivot. We recommend that you read What's New in PowerPivot and Add Calculations to Your Reports, Charts, and PivotTables.