Probably one of the most anticipated additions to the latest version of PowerPivot is the ability to create KPIs. PowerPivot has made what was once a task that was done purely done by developers now something that anyone can do now.
KPIs (Key Performance Indicator) are an executive favorite that help provide a quick at a glance look at how one area of business is doing. Typically they output some kind of indicator like a red, yellow, green light of some kind that can tell the report viewer quickly if a goal is being reached.
To create a KPI you must first create at least two calculated measures. The first one being the actual values and the second being the target or goal values the KPI is attempting to reach. In my example I?ve already created two DAX calculations to show profit and last year?s profit.
Profit:
=SUM(FactInternetSales[SalesAmount])-SUM(FactInternetSales[TotalProductCost])
ProfitLastYear:
=([Profit])(SAMEPERIODLASTYEAR(DimDate[FullDateAlternateKey]))
Now the simple part is creating the KPI. Select the measure that the KPI will be based on, in my case that is Profit, then in the PowerPivot tab click Create KPI.
This will open the Key Performance Indicator dialog box. The KPI base measure (value) should already be filled in for you with the measure you had selected when selecting Create KPI. Under Measure select the field the KPI should compare against. Optionally, you could provide an absolute value that could always be used in comparing. I want to try to beat last years sales so I select ProfitLastYear. The ranges on what determines if a value is in the red, yellow, or green is adjustable by either dragging or typing the percent you want. Notice the number of ranges and indicator types can easily be changed here as well.
After you hit OK the KPI is done! It?s automatically added to your report and your results would look something like this:
You can always adjust the KPI by hitting the Edit KPI Settings button in the ribbon. You can also create KPIs from the PowerPivot window in the Measure Grid.
Read my previous PowerPivot blog posts:
