Category: Power View

Data Model, Development Tips, Excel, How To, Information, Instalation & Configuration, Office Integration, PivotTable, Power View, PowerBI.com, PowerPivot

PowerPivot in Excel 2013

Ashwini Tripathi

I started the series in End of September and Starting of October on PowerPivot, Power View, PivotTable & Reports but in-between the release of Navision 2016 all the topics got scattered between other posts and I didn’t ended the topic.

Here I present all the posts link at one place which you can use as table of content for easy access and to help if any one wish to start from beginning and learn all the features & Topic on same.

PowerPivot for Excel

Start the Power Pivot in Microsoft Excel add-in

Troubleshooting: Power Pivot Ribbon Disappears

PowerPivot Creating a Data Model in Excel 2013

Adding more tables to the Data Model using Existing Connection – In PowerPivot

Add relationships to Data Model in PowerPivot

How to add Filter for data retrieval in PowerPivot Data model.

Create a calculated column in PowerPivot

Creating My First Report using PowerPivot

Basics of Power Pivot for Excel – 2013

Add Slicers to PivotTables in PowerPivot

Power View in Excel 2013

Import data using copy and paste from Excel sheet or other source for PowerPivot Data Model.

Add Excel Sheet/Table to the PowerPivot Data Model

Add a relationship using Diagram View in Power Pivot

Extend the Data Model using calculated columns

Create a hierarchy in PowerPivot Data Model

Use hierarchies in PivotTables

Create a Power View report

Create a calculated field in PowerPivot

Set field defaults in PowerPivot

Set Table Behaviour in PowerPivot

Set Data Categories for fields in PowerPivot

I will come up with more details once I get some time to explore and find anything which I feel is good to share with the community.

Till then keep Learning, Exploring and Practicing.

Advertisement
Data Model, Development Tips, Excel, How To, Power View, PowerPivot

Set Table Behaviour in PowerPivot

Ashwini Tripathi

You can set the default table behaviour, which Power View uses to automatically create report labels for the table.

This becomes useful when you create visualizations from the same table, perhaps for many different reports.

Open the Excel Workbook we created in our earlier post. Create a Power View report

We use default table behaviour in the next few steps, so let’s set it now.

Open Medals Table in Data Model, select Advanced > Reporting Properties > Table Behaviour. A window appears where you can specify table behaviour.
ExcelData-33

  • In the Table Behavior window, the Row Identifier is the column that contains only unique keys and no blank values. This is often the table’s primary key, but not compulsory to be any other field which satisfy this property. You have to select a Row Identifier before making other selections in the window. Select MedalKey as the Row Identifier.
  • In the Keep Unique Rows section, select AthleteID.

Fields you select here have row values that should be unique, and should not be aggregated when creating Pivot Tables or Power View reports.
Note: If you have trouble with reports that don’t aggregate how you want them, make sure that the field you want to aggregate is not selected in the Keep Unique Rows fields.

  • For Default Label, select a key that should be used as a default report label. Select Sport.
  • For Default Image, leave the selection as [No Column Selected], since you haven’t added images yet. The Table Behavior window looks like the following screen.

ExcelData-34

  • On the Power View sheet in Excel, select the table you created in the previous post Set field defaults in PowerPivot
  • From the ribbon, select DESIGN -> Table -> Card.

ExcelData-35

  • The table you created changes into a collection of Cards; the data is the same, but the visualization of the data has changed. The table now looks like the following screen.

ExcelData-36

Notice that the Sport field is larger than the rest, and appears as a heading for each card. That’s because we set Sport as the Default Label in the Table Behavior window when we were in Power Pivot.

I will come up with more details in my upcoming posts. Till then Stay Tuned, Keep Learning & Practicing.

Data Model, Development Tips, Excel, How To, Power View, PowerPivot

Set field defaults in PowerPivot

Ashwini Tripathi

When you set a default field set for a table, you can simply click that table in Power View, and the default set of fields is automatically added to a new report.

Open the Excel Workbook we created in our earlier post. Create a Power View report

In today’s post, we will learn how to set defaults for our workbook that will save our time when creating reports.

Steps to Create the Default Field Set for a table

  • Select the Medals table in Data View Mode from PowerPivot Manager.
  • From the Advanced tab, select Reporting Properties > Default Field Set. A window appears that lets you specify default fields for tables created using client tools such as Power View.
  • Select Sport, Event, EditionID, Athlete, and Medal in the left pane, and click Add -> to make them the default fields.

ExcelData-31

To see how this works, switch to the Power View sheet in Excel.

  • Click anywhere on the blank report canvas, to make sure you don’t have an existing visualization selected. Your Power View sheet currently only has one visualization, which is the map you created earlier in previous post. Create a Power View report
  • In the Power View Fields list, click the Medals table name. Power View creates a table and automatically adds the five default fields from the Medals table, in the order you specified, as shown in the following screen. Make sure you don’t click on the triangle beside Medals, if you do so the table simply expands, rather than adding a new table with default fields.

ExcelData-32

I will come up with more details on this topic in my upcoming posts. Till then Stay Tuned, Keep Learning and Practicing.

Data Model, Excel, How To, PivotTable, Power View, PowerPivot

Create a Power View report

Ashwini Tripathi

In the previous post, we created an Excel workbook with a PivotTable containing data about Olympic medals and events. If you didn’t saw the previous post you can access from here.

Use hierarchies in PivotTables

In this post, we will create a Power View report to visually represent the Olympics data.

In Excel, click INSERT > Reports > Power View Reports.
ExcelData-22

ExcelData-23

ExcelData-24
Select City from Host & Sport from Medal Table. Apply Count (Not Blank) to Sport Field using dropdown list next to Field Name.
ExcelData-25
From design switch the Visualization to Map.

On the map, blue circles of varying size indicate the number of different sport events held at each Olympic Host location.
ExcelData-26
Drag Season Field to Color area. This will add different colors for different Seasons. In our case summer/Winter Red/Blue colors.

Just in few clicks now we have a Power View report that visualizes the number of sporting events in various locations, using a map, color-coded based on season.

Will come with more details in my upcoming posts, till then stay tuned, keep learning and practicing.

Development Tips, Excel, How To, Power View, PowerPivot, Report

Power View in Excel 2013

Ashwini Tripathi

Power View is an interactive data exploration, visualization, and presentation experience that encourages intuitive ad-hoc reporting.

Power View is a feature of Microsoft Excel 2013, and of Microsoft SharePoint Server 2010 and 2013 as part of the SQL Server 2012 Service Pack 1 Reporting Services Add-in for Microsoft SharePoint Server Enterprise Edition.

Power View has these features, as part of Power BI for Office 365:

  • Create Power View sheets in Excel and then view them in the Power BI Windows Store app.
  • View Power View in Excel sheets in your browser, without installing Silverlight.

Data sources for Power View

In Excel 2013, you can use data right in Excel as the basis for Power View in Excel and SharePoint.

When you add tables and create relationships between them, Excel is creating a Data Model behind the scenes.

A data model is a collection of tables and their relationships reflecting the real-world relationships between business functions and processes—for example, how Products relates to Inventory and Sales.

You can continue modifying and enhancing that same data model in Power Pivot in Excel, to make a more sophisticated data model for Power View reports.

With Power View you can interact with data:

  • In the same Excel workbook as the Power View sheet.
  • In data models in Excel workbooks published in a Power Pivot Gallery.
  • In tabular models deployed to SQL Server 2012 Analysis Services (SSAS) instances.
  • In multidimensional models on an SSAS server (if you’re using Power View in SharePoint Server).

Recall from my previous post Creating My First Report using PowerPivot, I will be using same Data Model to demonstrate the feature of Power View, also same report in different format.

Let’s design Customer wise Sales Report using Power View.
PowerView-1

I am using the same Workbook which we used for PowerPivot creating Matrix report for Item Vs Customer Sales.

From Insert Tab choose Power View Reports in ribbon.

Remember this workbook already having Data Model with tables Customer, Item, Cust. Ledger Entry & Item Ledger Entry. One which we created during our previous exercise during walkthrough of PowerPivot.

We already have Relationship defined between Customer & Item Ledger Entry (No. -> Source No.), also Item & Item Ledger Entry (No. -> Item No.).
PowerView-2

You can View the Relationship using Relationship from Ribbon Power View Tab.

Once the Power View Sheet is inserted the Power View Tab will be Visible.

This will list the two relations which we created earlier in previous exercise post.

However we will be requiring one more relationship for this report. Add using New button on Manage Relationship window.

Enter the Relation for Customer & Cust. Ledger Entry (No. -> Customer No.).

You can see all the 4 tables are listed in Field List Pane.
PowerView-3
Arrange the Fields from respective tables as shown in above screenshot.
PowerView-4
Arrange the fields as shown in above screenshot.

Design of your report should be similar to one shown in below screenshot.
PowerView-5

Resize the Table area to fit the area and the way you want to represent the data.

When a Customer is selected in Title Area, below two tables show the Item Sales Quantity & Total Value for the selected Customer.

You can add fields in the Filter Pane to slice the data accordingly.

I will come up with more details in my upcoming posts.

Till then stay tuned and keep practicing.