While PowerPivot isn’t necessarily “new technology,” businesses are trying to move towards it due to how 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.
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 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:
Importing Data
Open Excel, click the PowerPivot 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 PowerPivot 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.
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:
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 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. Visit Microsoft’s Office site to find out more about KPI’s in PowerPivot or contact KTL Solutions if you would like to explore additional Power BI, data mining model, Microsoft GP and CRM solutions.
BARRY CROWELL, MBA | Senior Business Software Consultant and GP Lead
Barry uses his expertise to lead and teach a team of GP consultants to assure our solution scoping, architecting, and delivery meet and exceed the customer’s expectations. He helps clients analyze their current business processes and provides them with process improvements. He has performed implementations as the lead consultant and end-user project manager. Barry has over 20 years of experience working in accounting and the Microsoft Dynamics GP industry. He also possesses a bachelor’s degree in accounting and business administration from Black Hills State University and a master’s in business administration from La Salle University.
