Create a Data Model
Before you start Power View, your workbook must have a Data Model
The simplest way to have a data model is to click Insert > Table. This will reformat your data into an official (structured) Excel table, naming all the parts which are needed for the Data Model to be able to use it. But you can also have a data model from external database or Power Pivot.
Creating a data model doesn’t mean you have to have the data in your workbook. When you add data from the menu Data (Access, Text, From Other Sources), you can specify to create a connection only, but also add this data to the Data Model (this is the same Model used by Power Pivot).
So now, even if no data is visible in your workbook, you can use it in different tools like Power View and Power Pivot (since they consume data in your Data Model.) This is made possible by the Vertipaq engine, which is what drives the Model and built into Excel 2013.
Open Power View
Download the following file to follow along with the example. Since this file already has a Data Model you can click on Insert > Power View
A new worksheet is added in your workbook. This new worksheet is based on the SilverLight technology. It uses more memory than a classic worksheet but you can create interactive dashboards just by dragging & dropping objects on to it.
Create your first table in Power View
When your Power View worksheet opens, you can see all the fields of your differences sources of data on the right panel and also some data from your worksheets.
You can easily see the columns which have values, as they all have a Sigma symbol before the name of the field.
The initial table is just there to show what you can do. It’s better if you remove it and create your own table via the drag and drop interface like with a Pivot Table. (To do that, select the table and press DEL.)
Now, let’s create a new table where we will display the bonus by type and by city. So you just have to drag these 3 items from the Panel to the Fields zone, and it will create a new table for you!
At this point, the table is the same as a Pivot Table but when you go to the Table menu and click Matrix you convert your table into a hierarchical presentation
Drill-Down / Drill-Up
Once your have converted your table to a matrix, you can also convert it to let you drill between the fields of your table, allowing you to see the records that make up the totals. To do this, go to Show Levels > Rows – Enable Drill Down one level at the Time.
If you double-click the City name, or if you click on the arrow, the table will now drill in to show you the Type that makes up the City values! (Bangkok shown here.)