KPI

KPI (Key Performance Indicator) is the most common factor that shows the success of the company in any sphere of business, such as financial performance, customer confidence, employee satisfaction, process management … In Power Pivot reports KPI is obtained by comparing the existing measures with a goal value or another measure. When dragged in the report KPI is presented as a value, goal or status indicator (traffic light) showing whether the target is exceeded, realized or unrealized.

How to create a KPI will be shown on the example where we made a Power Pivot table based on sales data in the retail chain specialized in trade with alcoholic and non-alcoholic beverages. We made two measures to track sales of beer:

ProsecanPrometSvihPiva:=CALCULATE(AVERAGE([IZNOS]),Artikli[KLASIFIKACIJA]=”Piva”)

ProsecanPrometJelenPiva:=CALCULATE(AVERAGE([IZNOS]),Artikli[KLASIFIKACIJA]=”Piva”,Artikli[ROBNA MARKA]=”JELEN”)

First measure calculates average sales of beer, an second average sales of brand „JELEN“ beer. We’ll create a Power Pivot report in which will show theese measures according to place of delivery.

New KPI is created by selecting PowerPivot ribbon, and then in KPI menu an option New KPI. A new dialog box will open with title KPI (Key Performance Indicator).

First of all, should choose one of pre-made measures in drop-down list at the top of the window (KPI base field) on the basis of which we will create KPI. For example, it may be “ProsecanPrometJelenPiva” (Average sales of JELEN beer). Then choose the type of target value. Within Measure dropdown list should choose fixed value or another measure. For example, let’s take measure “ProsecanPrometSvihPiva”. In the end, it is necessary, by moving the slider, to define what percentage deviation compared to the goal value shows that goal is exceeded, realized or unrealized. Also, we can choose a set of icons that will be used for showing results.

After you create a new KPI it will appear as a field that exists within the Power Pivot report menu. You can drag it in values to show it in report, and it will be shown as Status (which can be -1, 0 or 1). Status indicates that the objective is exceeded, has or has not accomplished. Within a panel, KPI will be shown with a special icon that resembles traffic light. If you expand it, status will display three values: the value (Value), goal (Goal), and status (Status). By turning off, then on, a status „light indicator“ will be shown within a Power Pivot report.

In PowerPivot toolbar, inside the menu KPI, there is an option Manage KPIs. By activating this option will open a window where we can see all previously made key performance indicators. Choosing any of them, we can modify it or delete it. In this window we can also initiate the creation of a new KPI.