Power BI – Power Pivot – Creating your first data model and KPI

Part 3 of a 5 part series

While Power Pivot isn’t necessarily “new technology” I think businesses are trying to move towards it because Excel savvy end users can create their own reports without tying up IT resources. KPI’s are just another addition to Power Pivot that allows users to visually analyze data across millions of rows.

A KPI (Key Performance Indicator) is a graphical representation that displays progress against a predefined measure or business goal. KPIs make it easier for end users to evaluate the amount of progress without reading a bunch of data.

Enabling Power Pivot in Excel 2013

To enable Power Pivot, open Excel, go to File, Options, Add-Ins, select COM Add-ins and click GO. This will open up the COM Add-Ins dialog box. Click “Microsoft Office Power Pivot for Excel 2013” and hit OK. After successfully enabling Power Pivot, the tab should appear at the top of the Excel spreadsheet:


Importing Data

Open Excel, click the Power Pivot tab, Manage:


Upon clicking Manage, a new window should appear. From this window, you will import data. Click From Database and select From SQL Server:



Click Next, choose “Select from a list of tables and views to choose the data to import” and click Next. The next screen is where we will select our data to import. For this example, choose Patron and click “Select Related Tables”. The Select Related Tables button enables you to automatically select every table that is related to the source table selected:


After clicking Finish, the import will begin. Once the import finishes successfully you should be able to view all the tables separated into sheets:

Creating PivotTable

Before creating a KPI we will need to slice and dice our data into a PivotTable. To do this, click PivotTable on the ribbon bar and choose New Worksheet:


Casino Marketing wants a Power Pivot report that displays Average Daily Theoretical Win (ADT) on our player club members by City. Before we design the report we need to determine the calculation that we’ll need to get to this point. If I take the product cost and subtract it from the sales cost I’ll get my total profit in dollars. Then I’ll take that amount and divide it by the total product cost, which will give me the total percentage.

OK, easy enough. Let’s design the dashboard.

First, we need to slice the dashboard up into quarters since we only want to report on quarterly numbers. To do this, drilldown the drag down State into Filters section, City into the Rows section and TheoWin and GamingDays into the Values section.clip_image014

Create Calculated Fields

So far, so good. The next column we need to add will be a calculated column. We will need to determine the profit from each quarter. To determine the profit we will need to subtract the sales amount from the product cost.

Under the Power Pivot tab, click Calculated Fields and select New Calculated Field:


On the Calculated Field window select the table name, give the field a name, and enter your formula. For our example, we will use the Patron table, name it AveDailyWin, and enter our formula as =([Sum of TheoWin])/([Sum of GamingDays]). Select Number format and let it default to two decimal places.


In this example we used the outcome of Theoretical Win and divided it by the Total Gaming Days. After clicking OK, the new column should appear to the right:


Create KPI

The dashboard is almost is complete except for the KPI. To add a KPI click KPI’s, New KPI:


On the KPI screen we will need to choose the calculated field that we are basing our KPI values on. We will also choose absolute value because we didn’t create another calculated value to compare with. We will change the Absolute Value to 100 and move the thresholds like below.


After clicking OK, we notice that the KPI’s have been added to the right:



Next Steps

Creating basic Power Pivot dashboards are fairly easy and usually don’t require any IT resources except to provide a data model and possibly security access to the database for importing data. To find out more about KPI’s in Power Pivot click here to visit Microsoft’s Office site or follow my blog to explore additional Power BI, data mining model, Microsoft GP and CRM solutions.