Microsoft® SQL Server® 2012 Bible

1308

Part IX: Business Intelligence

Creating KPIs in PowerPivot

KPIs provide a quick and effective way to evaluate success of an organization’s activ-

ity. For example, you can create a KPI that evaluates Reseller Sales Gross Profi t from the

AdventureWorksDW2012 database. The Reseller Sales Gross Profi t formula is as follows:

Reseller Sales Gross Profi t = Sales Amount − Total Product Cost

In this example, Sales Amount should be greater than the Total Product Cost to earn a

profi t. If Sales Amount is less than Total Product Cost than, the business loses money. You

want to create a KPI that displays a green fl ag if there is a profi t or a red fl ag if there is a

loss on the reseller sales transactions. To create a KPI to track Reseller Sales Gross Profi t,

fi rst create a calculated column named Gross Profi t as follows:

1. Follow steps 1 to 10 from the section “Creating BI Semantic Models Using
   PowerPivot.”


2. Click the FactResellerSales tab.


3. Scroll all the way to the right until you see a blank column with Add Column as
   header name.


4. Click on the Add Column header and position the mouse cursor inside the formula
   bar.


5. Type the following formula inside the formular bar
   =[Sales Amount]-[Total Product Cost] and press enter.


6. Right-click the header and select Rename to rename the column as Reseller Sales
   Gross Profi t. Figure 58-9 shows the Reseller Sales Gross Profi t calculated column.


7. Click the Reseller Sales Gross Profi t header and create a measure by clicking the
   Autosum option from the Offi ce Ribbon. The new measure name Sum of Reseller
   Sales Gross Profi t displays below the Reseller Sales Gross Profi t column.


8. Click the Sum of Reseller Sales Gross Profi t measure and create a KPI by clicking the
   Create KPI option from the Offi ce Ribbon. The Key Performance Indicator (KPI) win-
   dow opens.


9. Check the option Absolute value under the Defi ne target value section. Replace the
   value with the number zero and press the TAB key. The status thresholds recalcu-
   late the base on the new zero value.

c58.indd 1308c58.indd 1308 7/31/2012 10:36:02 AM7/31/2012 10:36:02 AM

http://www.it-ebooks.info