Tag: PowerPivot

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, PowerPivot

Set Data Categories for fields in PowerPivot

Ashwini Tripathi

In order for Power View to dynamically create reports based on underlying data, such as location, fields that contain such data must be properly categorized.

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

For the Olympics data, let’s specify the categories for a few fields.

  • In Power Pivot, select Hosts. Select the NOC_CountryRegion field. From Advanced -> Reporting Properties -> Data Category: click the arrow and select Country/Region from the list of available data categories, as shown in the following screen.

ExcelData-37

  • Return to Excel, and select the Power View sheet. Expand the Medals table in Power View Fields, and notice that the NOC_CountryRegion field now has a small globe icon beside it. The globe indicates that NOC_CountryRegion contains a geographic location, as shown in the following screen.

ExcelData-38

We’ll use that geographic location in an upcoming post. It’s time to save your work, review what you’ve learned, and then get ready to dive into the next post.

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

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, Development Tips, Excel, How To, PowerPivot

Create a calculated field in PowerPivot

Ashwini Tripathi

Open the Excel workbook which we used in our previous post on this topic. Olympics Excel Work book.

Select PowerPivot -> Manage

PowerPivot Window will open, If Calculation area not visible below table data as shown in below screen. Click Calculation Area button in Top Right Corner.

ExcelData-27

In the Calculation Area, select the cell directly below the Edition column. From the ribbon, select AutoSum > Distinct Count, as shown in the following screen.
ExcelData-28

Power Pivot automatically created the following DAX formula: Distinct Count of Edition:=DISTINCTCOUNT([Edition])

Additional calculations in AutoSum are just as easy, such as Sum, Average, Min, Max, and others.

Let’s calculate the percentage of all medals. In the formula bar, type the following DAX formula. IntelliSense provides available commands based on what you type, and you can press Tab to select the highlighted IntelliSense option. Percentage of All Medals:=[Count of Medal]/CALCULATE([Count of Medal],ALL(Medals))
ExcelData-29

When we return to Excel Sheet we can find these fields available in Medal Table. Shown in the right side of the screen above.

Let’s create a PivotTable from Medal Table. Our Pivot Table will be designed as below screen.
ExcelData-30

I will come up with more details inn my upcoming posts. Till then stay tuned, keep Learning and keep practicing.

Data Model, Development Tips, Excel, How To, PowerPivot, Report

Create a hierarchy in PowerPivot Data Model

Ashwini Tripathi

We will be using the Excel workbook we used in our earlier posts. Open the Excel file, you can find the link for download in my earlier posts or from blog Menu.

SharePath

Most Data Models include data that is inherently hierarchical. The Olympics data is also hierarchical. It’s helpful to understand the Olympics hierarchy, in terms of sports, disciplines, and events.

For each sport, there is one or more associated disciplines (sometimes there are many).

And for each discipline, there is one or more events (again, sometimes there are many events in each discipline).

The following Table illustrates the hierarchy.

ExcelData-13

In this post we will create two hierarchies within the Olympic data. Then use these hierarchies to see how hierarchies make organizing data easy in PivotTables and in Power View in upcoming posts.

Create a Sport hierarchy

In Power Pivot, switch to Diagram View. Expand the Events table so that you can more easily see all of its fields.

ExcelData-14

  • Press and hold Ctrl, and click the Sport, Discipline, and Event fields. With those three fields selected, right-click and select Create Hierarchy. A parent hierarchy node, Hierarchy 1, is created at the bottom of the table, and the selected columns are copied under the hierarchy as child nodes. Verify that Sport appears first in the hierarchy, then Discipline, then Event.

ExcelData-15

  • Double-click the title, Hierarchy1, and type SDE to rename your new hierarchy. You now have a hierarchy that includes Sport, Discipline and Event. Your Events table now looks like the above screen.
  • Still in Diagram View in Power Pivot, select the Hosts table and click the Create Hierarchy button in the table header, as shown in the following screen.

ExcelData-16

  • An empty hierarchy parent node appears at the bottom of the table.
  • Type Locations as the name for your new hierarchy.
  • There are many ways to add columns to a hierarchy. Drag the Season, City and NOC_CountryRegion fields onto the hierarchy name (in this case, Locations) until the hierarchy name is highlighted, then release to add them.
  • Right-click EditionID and select Add to Hierarchy. Choose Locations.
  • Ensure that your hierarchy child nodes are in order. From top to bottom, the order should be: Season, NOC, City, EditionID. If your child nodes are out of order, simply drag them into the appropriate ordering in the hierarchy. Your table should look like the above screen.

Your Data Model now has hierarchies that can be put to good use in reports. In the upcoming posts we will learn how these hierarchies can make our report creation faster, and more consistent.

Stay tuned for more details, will come up with usage of hierarchy in my upcoming post.

Till then keep learning & practicing.

Data Model, Development Tips, Excel, How To, PowerPivot, Report

Add a relationship using Diagram View in Power Pivot

Ashwini Tripathi

Open the Workbook which we prepared in the last exercise in our previous post.

Add Excel Sheet/Table to the PowerPivot Data Model

  • In the Power Pivot window, in the View section, click Diagram View.
  • Use the slide bar to resize the diagram so that you can see all objects in the diagram. Rearrange the tables by dragging their title bar, so they’re visible and positioned next to one another. Notice that all tables are unrelated to each other, we will create relationship among these tables.

After adding relationship the Diagram will Look as below.

ExcelData-6

  • Let start creating the relationship.
  • Sports->SportID click and drag to Disciplines->SportID and release. You will see the relation is built between these two tables on SportID.

Similarly follow to create other relations too as shown in below image.
ExcelData-5

You notice that both the Medals table and the Events table have a field called DisciplineEvent. Upon further inspection, you determine that the DisciplineEvent field in the Events table consists of unique, non-repeated values.

The DisciplineEvent field represents a unique combination of each Discipline and Event. In the Medals table, however, the DisciplineEvent field repeats many times. That makes sense, because each Discipline+Event combination results in three awarded medals (gold, silver, bronze), which are awarded for each Olympics Edition the Event is held. So the relationship between those tables is one (one unique Discipline+Event entry in the Disciplines table) to many (multiple entries for each Discipline+Event value).

  • Create a relationship between the Medals table and the Events table. While in Diagram View, drag the DisciplineEvent field from the Events table to the DisciplineEvent field in Medals. A line appears between them, indicating a relationship has been established.
  • Click the line that connects Events and Medals. The highlighted fields define the relationship, as shown in the above screen.
  • To connect Hosts to the Data Model, we need a field with values that uniquely identify each row in the Hosts table. Then we can search our Data Model to see if that same data exists in another table. Looking in Diagram View doesn’t allow us to do this. With Hosts selected, switch back to Data View.
  • After examining the columns, we realize that Hosts doesn’t have a column of unique values. (Assume EditionID Field is not available in the Host & Medal Table, in my case I have already added this Field in my workbook I shared). In such case we’ll have to create it using a calculated column, and Data Analysis Expressions (DAX).

It’s nice when the data in your Data Model has all the fields necessary to create relationships, and mash up data to visualize in Power View or PivotTables. But tables aren’t always so cooperative, so the next post will describe how to create a new column, using DAX that can be used to create a relationship between tables.

We will see how to work with calculated fields in our next upcoming post.

Data Model, Development Tips, Excel, How To, PowerPivot, Report

Add Excel Sheet/Table to the PowerPivot Data Model

Ashwini Tripathi

Download the Excel file from the link provided in previous post.

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

Open the Workbook:

ExcelData-3

Select sheet one by one and Add to Data model of PowerPivot.

ExcelData-4

Make sure all Table/Sheet is added to Data Model of PowerPivot.

I have create a sample Excel File for you which you can down load from the Shared Path using link: http://1drv.ms/1GpZFd4

This file have couple of sheets having data related to Olympics which I have compiled from MS Office support site.

You can find the link on the Menu of my blogsite too to access the shared files.

I will be using this file for couple of walkthrough in my upcoming posts.

Data Model, Development Tips, Excel, PowerPivot

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

Ashwini Tripathi

You can copy & Paste data from an Excel workbook, or import data from a table on a web page, or any other source from which we can copy and paste into Excel. In the following steps, you add the data from a table.

  • Insert a new Excel worksheet, and give it the name desired any meaningful name that describes the data.
  • Select and copy the data, including the table headers.
  • In Excel, place your cursor in cell A1 of the above created worksheet and paste the data.
  • Format the data as a table. You press Ctrl + T to format the data as a table, or from HOME > Format as Table. Since the data has headers, select My table has headers in the Create Table window that appears.

ExcelData-1

  • Name the table. In TABLE TOOLS > DESIGN > Properties locate the Table Name field, and type desired name.

ExcelData-2

  • Format your data using Excel std. formatting tool.
  • Save the workbook.This file have couple of sheets having data related to Olympics which I have compiled from MS Office support site.
  • You can find the link on the Menu of my blogsite too to access the shared files.
  • I have create a sample Excel File for you which you can down load from the Shared Path using link: http://1drv.ms/1GpZFd4

I will be using this file for couple of walkthrough in my upcoming posts.

Development Tips, Excel, How To, PowerPivot, Report

Add Slicers to PivotTables in PowerPivot

Ashwini Tripathi

Recall from my earlier post Creating My First Report using PowerPivot in which we created a Items Vs Customer Sales matrix report.

I am going to use same report to demonstrate how we can add slicer to this report.

Slicers are one-click filtering controls that narrow the portion of a data set shown in PivotTables and PivotCharts. Slicers can be used in both Microsoft Excel workbooks and PowerPivot workbooks, to interactively filter and analyze data.
PowerPivot-30

Open the report we created in our previous post.

From Insert Tab select Slicer in the ribbon.
PowerPivot-31

Choose the source Connection/Data model and respond Open.

Select the Customer Table.

Select the Country/Region Code field and OK.
PowerPivot-32

A Slicer will be added to the sheet.

Re-size to fit and drag the Slicer to position at desired location on sheet.

You can give desired Caption to your Slicer by editing the Slicer Caption.

Most important is to select the pivot table on which this Slicer will operate.

Select Report Connection, and from preceding window select the PivotTable.
PowerPivot-33
Above is the screenshot with applied filter on Country/Region Code = DE.

Stay tuned to know more options, I will come up with more details in my upcoming posts.

Till then keep practicing.