Debunking the Myths: Cloud HA and DR common misconceptions

Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips
































Top SQL Server Tools



































   Got a SQL tip?
            We want to know!

Building Key Performance Indicators (KPIs) with PowerPivot

MSSQLTips author Brady Upton By:   |   Read Comments (1)   |   Related Tips: More > Microsoft Excel Integration
Problem

While PowerPivot 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 PowerPivot that allows users to visually analyze data across millions of rows.

In a previous tip I explained how to use PowerPivot with Excel 2013. This tip will focus on creating calculated fields and KPI's in PowerPivot.  Check it out.

Solution

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.

If you are new to PowerPivot, try looking over some of these tips first to gain a foundation on what PowerPivot is and some of the basics of creating dashboards.

In this example, I'll use AdventureWorksDW2012 sample data so you can follow along with me. The database can be downloaded here.

Let's get started.

Enabling PowerPivot in Excel 2013

To enable PowerPivot, 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 PowerPivot for Excel 2013" and hit OK. After successfully enabling PowerPivot, the tab should appear at the top of the Excel spreadsheet:

Enabling PowerPivot in Excel 2013

Importing Data

Open Excel, click the PowerPivot tab, Manage:

Open Excel, click the PowerPivot tab and the Manage icon

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

Click From Database and select From SQL Server

Type in the Server Name, Authentication mode, and browse to the AdventureWorksDW2012 database:

PowerPivot Table Import Wizard to connect to a Microsoft SQL Server Database

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 FactInternetSales 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:

Table Import Wizard to Select Tables and Views

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

View all the tables separated into worksheets in PowerPivot

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:

Creating a PivotTable

Our boss wants a PowerPivot report that displays quarterly profit percentages on AdventureWorks' total sales. 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 DimDate header and drag down CalendarYear and CalendarQuarter into the Rows section: (Make sure CalendarYear is on top of CalendarQuarter)

Let's design the dashboard and start with the quarterly time periods

We now have a PivotTable with something on it. Next we need to add some values. Drilldown the FactInternetSales header and drag SalesAmount down to the Values section:

Add SalesAmount to the Values section of the worksheet

Our Pivot table is coming along. We now have the total sales amount broken down by each quarter. Next we'll need to add the total product cost. Drag TotalProductCost to the Values section below SalesAmount:

We now have the total sales amount broken down by each quarter.

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 PowerPivot tab, click Calculated Fields and select New Calculated Field:

Create Calculated Fields

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 FactInternetSales table, name it TotalProfit, and enter our formula as =SUM(FactInternetSales[SalesAmount])-SUM(FactInternetSales[TotalProductCost])

the Calculated Field window for the TotalProfit column

We have now created a calculated field that will subtract the sales amount from the product cost. After clicking OK, the new column should appear to the right:

The new column should appear to the right

Now we need to create a calculated field that will give us the percentage of profit. To do this, we will need the outcome of the above calculated field.

Choose New Calculated Field and enter the following:

  • Table name: FactInternetSales
  • Calculated field name: ProfitPercentage
  • Formula: =[TotalProfit]/SUM(FactInternetSales[TotalProductCost])
Build the ProfitPercentage column

In this example we used the outcome of TotalProfit and divided it by the Total Product Cost. After clicking OK, the new column should appear to the right:

For the ProfitPercenage, we use the TotalProfit and divided it by the Total Product

To format the calculated columns, highlight the column and right click and choose Format Cells:

Format the cells into dollars and cents

Create KPI

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

Create KPI in PowerPivot

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 1 and move the thresholds like below. The status thresholds are reporting on the percentage in decimal form, for example, 70% looks like .70.

Calculated field that we are basing our KPI values

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

Final calculations with the KPIs in PowerPivot
Next Steps
  • To find out more about KPI's in PowerPivot click here to visit Microsoft's Office site.
  • Creating basic PowerPivot 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.
  • You can find other tips regarding Microsoft Excel Integration here.


Last Update: 3/5/2013


About the author
MSSQLTips author Brady Upton
Brady Upton is a Database Administrator and SharePoint superstar in Nashville, TN.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Comments and Feedback:
Monday, September 02, 2013 - 10:42:57 AM - Haad Read The Tip

 

Dear Brady Baron, 

Source Filehttp://www.mssqltips.com/sqlservertip/2879/building-key-performance-indicators-kpis-with-powerpivot/

 

 

hey why does "calculated field" in this example not work , only works in excel 2013 . The field is not visible in excel 2010

or am I doing something wrong , can you please help me it is really important.

 

Thank you for your help

 

Haad

 

 



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.