Important: In Excel for Microsoft 365 and Excel 2021, Power View is removed on October 12, 2021. As an alternative, you can use the interactive visual experience provided by Power BI Desktop, which you can download for free. You can also easily Import Excel workbooks into Power BI Desktop.
You created a Data Model in Tutorial: Import Data into Excel, and Create a Data Model, bringing together data in multiple tables and from different data sources. Now you can enhance the Data Model so it can be the basis of sophisticated Power View reports.
-
Open the workbook you created in Tutorial: Import Data into Excel, and Create a Data Model.
-
In Excel, on Power Pivot > Manage to open the Power Pivot window.
If you don’t see the Power Pivot tab, see Start Power Pivot in Excel 2013 add-in for instructions.
Set default fields
In the Power Pivot window
-
Click the DimProduct table tab.
-
On the Advanced tab > Default Field Set. The default fields are the ones added to a Power View sheet when you click the table name rather than expanding the table and selecting specific fields. The dialog box lists all the fields in the table, even the ones you have marked to hide from client tools. If you add one of the hidden fields to the default field set, it still won’t appear in client tools.
-
Select and add these fields:
-
ProductName
-
Product Category
-
Product Subcategory
-
UnitCost
-
-
Click OK.
In the Power View sheet in Excel
-
Go back to the Power View sheet in Excel. You see a message that the Power View report needs fresh data. Click OK. For the rest of this tutorial, every time you change the Data Model in Power Pivot, you need to refresh the data in the Power View sheets.
-
Click the blank sheet (not selecting the map you added in the other tutorial) and click the DimProduct table name.
Power View adds a table with those four fields.
Set table behavior
In the Power Pivot window
-
Go back to the Power Pivot window and the DimProduct table tab.
-
Click Table Behavior.
-
For Row Identifier, click ProductKey.
If you don’t set a row identifier, you can’t set any of the other values in this dialog box.
Note: The dialog box lists all the fields in the table, including ProductKey, which you marked to hide from client tools. If you set one of the hidden fields as the default label, it still won’t appear in client tools.
-
For Keep Unique Rows, select ProductName.
-
For Default Label, select ProductName. There are no images in this data, so you can’t set a default image.
-
Click OK.
In the Power View sheet in Excel
-
Go back to the Power View sheet in Excel and refresh the data. Note that in the Field list and the Fields box, Product name has a little card-like icon next to it.
-
Select the table you created with the default fields in the previous section.
-
On the Design tab, click the arrow under Table and click Card. Note that the cards contain the same fields as the table, but they’re laid out differently. For example, the field we set as the default label, Product Name, is displayed more prominently than the text in the other fields. You can change the way the other text is displayed in the next step.
-
On the Design tab, click the arrow under Card Style and click Callout. Now all the text is large.
Read more about why you configure table behavior properties for Power View reports.
Create default aggregates
In the Power Pivot window
-
Click the FactSales table tab.
-
Click the box under the UnitPrice column in the Calculation Area.
-
On the Home tab, click the arrow next to AutoSum > Average.
-
This creates a calculated field that calculates the average of the unit price of a product, based on the context of the calculation – the location of the field in a visualization.
In the Power View sheet in Excel
-
Go back to the Power View sheet in Excel and refresh the data.
-
Expand the FactSales table in the Field List.
-
Note the different icons next to the fields. The UnitPrice field has a Sigma (Σ) symbol next to it. The Sum or UnitPrice field has a little calculator symbol. That’s the calculated field. Click the blank canvas and then select the UnitPrice and Sum of UnitPrice fields.
-
The two values are the same.
-
In the Fields area, click the arrow next to the UnitPrice field. Note the different options: Sum, Average, etc. Click Average.
-
In the Fields area, click the arrow next to the Sum of UnitPrice field. Note you can’t change the aggregate because you defined the aggregation for this field in Power Pivot.
Tip: Keep this in mind when developing your data model: Power View can do lots of simple calculations for you on the fly, with lots of flexibility. So let Power View do the simple calculations. Only create the more complicated calculations in the model in Power Pivot.
Add descriptions
You can add descriptions to fields and tables in Power Pivot. They are displayed in Power View.
In the Power Pivot window
-
In the DimProduct table, select the ProductName column, right-click and then click Description.
-
Type “This is what we call the product” or anything else you’d like.
-
Right-click the Geography table tab, and then click Description.
-
Type “This is the location” or anything else you’d like.
In the Power View sheet in Excel
-
Go back to the Power View sheet in Excel and refresh the data.
-
Hover over the Geography table name and the ProductName field to see the descriptions you added.
