Key Performance Indicators - Excel Power View

What is Key Performance Indicators?

A Key performance Indicator (KPI) is a quantifiable dimension for gauging enterprise targets. Examples of KPIs are −

  • income branch of an company use a KPI to degree monthly gross income in opposition to projected gross profit.
  • Accounting department measure month-to-month prices in opposition to sales to evaluate prices.
  • Human assets department degree quarterly employee turnover.
  • commercial enterprise experts frequently use KPIs that are grouped collectively in a enterprise scorecard to attain a brief and accurate historical summary of business fulfillment or to identify trends.

KPIs are a form of verbal exchange related to the following sports −

  • identifying the KPIs based totally on the company’s objectives.
  • tracking and reporting the KPIs.
  • changing the KPIs as the organization progresses and / or the agency’s desires exchange.

the second one and 0.33 goals given above can be done with strength View. A KPI consists of Base value, target value/ intention and status.

Base cost

A Base cost is described by using a calculated field that resolves to a value. The calculated field represents the modern fee for the object in that row of the desk or Matrix. as an instance, combination of income, earnings for a given duration, and many others.

Target value

A goal value (or aim) is described via a calculated discipline that resolves to a value, or by an absolute price. The current price is evaluated in opposition to this cost. this may be one of the following

a hard and fast wide variety this is the intention all the rows have to achieve. E.g. sales goal for all of the salespersons.

A calculated area that would have a different purpose for each row. as an example, price range (calculated area), branch-wise in an company.

Fame

status is the visual indicator of the price. In power View, you could edit the KPI, choosing which indicators to use and what values to trigger each indicator.

Figuring out the KPIs

the first and the most critical step in KPI analysis is to become aware of the KPIs that efficaciously display the specified traits in the employer. This calls for whole know-how of the goals and calls for proper communique channels between the analysts and people who're liable for gratifying the targets.

There are a number of KPIs to select from, but the achievement in tracking is predicated on the proper preference of those which can be applicable to the targets. The KPIs range from corporation to company and from department to department. it's miles powerful simplest after they result in development within the performance.

you can evaluate the relevance of a KPI using the clever standards, i.e. the KPI must be particular, Measurable, doable, relevant and Time-sure. In different phrases, the KPI selected should meet the following criteria −

  • The KPI reflects your specific objective.
  • The KPI enables you to measure progress in the direction of that intention.
  • The aim for which the KPI is being defined is realistically manageable.
  • The purpose that the KPI is focused on is applicable to the employer.
  • you may set a Time-frame for reaching the goal in order that the KPI well-knownshows how close to the goal is as compared to the time that is left.

The defined KPIs are to be evaluated occasionally to find their relevance because the time progresses. If required, specific KPIs need to be described and monitored. The KPIs may need to be edited as the time progresses. best then, your KPI monitoring may be referring to the modern-day organization desires.

Defining KPIs in the information version

when you pick out the KPIs, you may outline them within the information version and add them on your electricity View file to show the fame of the set desires. you could do any of the subsequent −

  • outline the KPIs in the statistics version and use them as fields in power View to visualize them.
  • outline and / or edit the KPIs in electricity View.

you will learn how to define KPIs inside the information version in this segment. the other method you will examine inside the next phase.

assume you need to display the income targets of the salespersons in an employer who are selling a product. The objective is to pick out the great performers who are assembly the income goal. you can proceed to outline the KPI as follows −

Base cost − modern-day value of the sales quantity for each shop clerk.

goal fee / intention − target price is constant for all of the salespersons to be able to enable comparison between the salespersons. count on that the sales goal is 3500.

popularity − The fame is to be displayed with a picture to without difficulty decide the popularity of the bottom value compared to the target fee.

Define the KPI in the records model as follows −

  • recollect the 2 data tables - salesperson and income.
  • Create a dating among the 2 facts tables the usage of the field salesperson identification.

kpi_data_model

o set the Base Value, you need a calculated field for Sales Amount.

  • Click the Data View in the Power Pivot window.
  • Click in the cell at the bottom of the column Sales Amount
  • Type the following DAX formula in the formula bar.

Total Sales:=SUM([Sales Amount])

formula_bar

  • Click in the Excel window.
  • Click the POWERPIVOT tab on the Ribbon.
  • Click KPIs in the Calculations group.
  • Select New KPI from the dropdown list.

kpi_powerpivot

The Key Performance Indicator (KPI) dialog box appears.

  • Select Total Sales in the KPI base field (value) box.
    • Under KPI Status, do the following −
    • Under Define target value, select Absolute value and type 3500 in the box.
    • Under Define status thresholds, adjust the vertical bars representing the percentages to 40 and 80.
  • Under Select icon style, select the first option.
  • Click OK.

kpi_base_field

To check the KPI in the Data Model, do the following −

  • Click in the Power Pivot window.
  • Select the diagram view.

total_sales

The Total Sales appears as a KPI field in the Sales data table.

Visualization of KPIs in Power View

Create a Power View sheet to visualize the KPI that you have defined.

You will observe the following in the Sales table in Power View Fields list −

  • Total Sales field is a KPI and is depicted by the icon Icon.
  • The three KPI parameters – Value, Goal and Status appear as fields under Total Sales KPI.
  • Select the three fields – Value, Goal and Status under the KPI Total Sales from the Sales table.
  • Select the field Salesperson from the Salesperson table.

Table visualization appears by default.

pivot_view_fields

The three KPI parameters appear as columns in the Table, with the Status column displaying the icons as per the corresponding values.

Editing KPIs in Power View

You can also define the KPI thresholds by values instead of percentages. To modify the defined KPI Total Sales, proceed as follows −

  • Click the POWERPIVOT tab on the Ribbon.
  • Click KPIs in the Calculations group on the Ribbon.
  • Select Manage KPIs from the dropdown list.

manage_kpi

  • The Manage KPIs dialog box appears.
  • Click KPI – Total Sales.
  • Click the Edit button.

manage_kpi_dialog_box

The Key Performance Indicator (KPI) dialog box appears.

  • Under Define status thresholds, adjust the vertical bars to 1500 and 3000.
  • Retain the rest of the earlier options and click OK.

key_performance_indicator

Click Close in the Manage KPIs dialog box.

Power View displays a message that the Data Model is changed. Click OK.

kpi_power_view

You can see that the status icons reflect the changed thresholds

status_icon

Defining KPIs in Power View

You can define a KPI from the Power View sheet itself. Suppose you do not have the calculated field Total Sales in the Data Model. You can create that from Power View itself.

  • Create a new Power View sheet.
  • Click the POWERPIVOT tab on the Ribbon.
  • Click Calculated Fields in the Calculations group on the Ribbon.

Select New Calculated Field from the dropdown list.

new_calculated_field

The Calculated Field dialog box appears.

Fill in the details for the calculated field as shown below and click OK.

calculated_field_kpi

The calculated field Total Sales appears in the Sales table in the Power View Fields list.

You can identify it as a calculated field as it appears with a calculator Calculator calculator_iconicon.

new_calculated

Now, you can use this calculated field to define a KPI from Power View.

  • Click the POWERPIVOT tab on the Ribbon.
  • Click KPIs in the Calculations group on the Ribbon.
  • Select New KPI from the dropdown list.

new_kpi

The Key Performance Indicator (KPI) dialog box appears.

  • Select Total Sales in the KPI base field (value) box.
  • Under KPI Status, have the following options −
    • Under Define target value, select Absolute value and type 3500 in the box.
    • Under Define status thresholds, adjust the vertical bars representing the percentages to 40 and 80.
    • Under Select icon style, select the first option.
  • Click OK.

kpi_base_field_values

Total Sales appears as a KPI depicted by the icon KPI Iconwithkpi_icon the three KPI parameters – Value, Goal and Status as fields under it.

total_sales_kpi

Aesthetic Reports with KPIs in Power View

You can create an aesthetic report of Sales Performance with KPIs in Power View as follows −

  • Create a Table with the fields – Salesperson, Total Sales and Total Sales Status.
  • Click below the Table in Power View.
  • Create a Table with the fields – Salesperson, Total Sales and Total Sales Goal.
  • Switch visualization to 100% Stacked Bar Chart.
  • Click next to the Table in Power View.
  • Create a Table with the fields – Salesperson, Region, Total Sales and Total Sales Status.
  • Switch visualization to Card.
  • Drag the field Region to Tile By.
  • Type the Title – Sales Performance for the Power View Report.

Resize Table, 100% Stacked Bar Chart and Card for conspicuous visualization.

sales_performance

Observe that in Power View, you could portray the results as follows −

  • Table with icons for KPI status.
  • 100% Stacked Bar Chart depicting the percentage achieved with respect to the Goal. You can also notice that it gives a clear comparison of the performance of all the Salespersons.
  • Card visualization depicting the KPI status of the Salespersons along with the Region, which they belong. You can interactively scroll through the Tiles to display results for different Regions that would give scope to assess performance regionwise also.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Excel Power View Topics