This week I look at creating KPIs in PowerPivot 2012. KPIs are built using measures and in most cases, calculated measures that you define with DAX. PowerPivot 2008 had the ability to create calculated measures as we saw several weeks ago when I created a calculated measure to display the distinct count rather than a simple count. However, the calculated measures I will use here are a bit more difficult and show some of the additional power of DAX. Remember PowerPivot does not evaluate calculated measures for every row in a table. Rather, Excel only calculates the measures needed for display in a pivot table.
For this example, I’ll use the Contoso 2012 sample data that I used last week. This time I will calculate KPI’s based on the growth of each individual product sales quantity. Management has decided not to look at the growth in sales amount because price increases could indicate growth even when the quantities of a product sold actually decreased.
Open the Contoso 2012 data in Excel and navigate to the PowerPivot window. Click on the tab for the table where you want to add the measure, FactSales for this example. In the Home ribbon, select the Calculation Area button in the View group if it is not already selected. This action splits the screen into an upper portion that displays the table data and a lower portion that appears blank. This lower portion is where you define measures in PowerPivot 2012 instead of in the Field List of the pivot page itself. This means that the calculated measures are available in every table and chart that reference the table even though you have only defined the measure in only one place.
To create a new measure, click in any empty cell of this lower portion. I generally place my calculated columns along the leftmost column, but there is nothing wrong with placing them under the primary column used in the calculation if that makes more sense to you.
After selecting the cell, the action moves to the equation bar at the top of the screen. Begin defining the measure definition by entering the name of the calculated measure followed by the two characters ‘:=’. These characters tell Excel that you are defining a calculated measure. In this case, I want to define my measure as the sum of the product quantities by product. To do this I can use the CALCULATE() function along with the SUM() function.
The CALCULATE() function begins with a parameter that defines the calculation. In my case, I want to sum SalesQuantity. The second parameter defines over what attribute I want to sum SalesQuantity. Here I will use ProductKey rather than Product Name just in case Product name is not unique across all Product Key values. Therefore, my DAX expression for this measure is:
:= CALCULATE(SUM[SalesQuantity]), DimProduct[ProductKey])
Notice that I did not prefix the field [SalesQuantity] with a table name because this field is in the current table, but I did prefix [ProductKey] with the table name DimProduct because I was referencing another table. The following figure shows this expression.
For this example, I want to compare the current sales with the sales from the prior quarter. To do this, I can create another calculated measure defined as:
LastQtrQty = CALCULATE([CurrentProductQty], dateadd(DimDate[DateKey], -1, quarter)
Notice that this expression uses the DateAdd() function to calculate a date in the previous quarter from the current date by using the second parameter of -1 and the third parameter of ‘quarter’ to tell the function which dates should be filtered.
Ok, you might be asking how does [CurrentProductQty] know I wanted to sum the data by quarter? Actually, it doesn’t, but when I build the pivot table, I will use the Calendar Quarter slicer to display the data by quarters.
Finally, I need a ratio to compare the current product quantity sold to the quantity sold from the last quarter to see growth. I can do that with the following calculated measure.
QtrGrowth = ([CurrentProductQty] – [LastQtrQty])/[LastQtrQty]
The following figure shows this calculated measure formula. With this formula, a value of ‘0’ indicates no growth, but no loss. A negative value indicates that sales quantities have decreased, and a positive value indicates that sales growth has increased.
While I am in the PowerPivot window, I can tell Excel how to format these calculated measures. As suggested earlier, defining the format of the measure here can save you time because the calculated measure can be used by any sheet in the current workbook that references the table. Right click the measure and select Format to display the format dialog
In the Formatting dialog, I can select the field type category, and then the specific format definition. In this case because I am talking about quantities, I will format the data using the Number category, the Decimal Number format, zero decimal places and the comma thousands separator for both of the quantity calculated measures.
For the growth rate, I am calculating a ratio and therefore want to select the Percentage format with 3 decimal places and I’ll ignore the 1000s separator.
The growth rate measure is also my KPI for this example. When my ratio is less than zero (last quarter sales quantity is greater than this quarter sales quantity), I can say that sales of that product have decreased. When my ratio is greater than zero (last quarter sales quantity is less than this quarter sales quantity), I can say that sales of that product have increased. To define a KPI using the QtrGrowth measure, I need to right click the measure and select Create KPI from the popup menu.
In the Key Performance Indicator dialog, I need to define how I want to calculate the KPI. First I need to decide whether I want to compare the current measure to another measure or to compare it to an absolute value. In this case, I want to compare my growth rate to an absolute value and that value is zero. Basically, values less than zero are bad and values greater than zero are good. However, I do not need to limit myself to an either/or comparison. In fact, I may only want to flag decreases of more than 10% in the sales quantity as bad and to flag increases of more than 10% in the sales quantity as good. Everything in between these two thresholds I will just label as satisfactory.
As you can see in the figure below, I defined the thresholds for bad and good data as -10% and 10% respectively. Note that I represent 10% with the decimal value of 0.10. I can also select one of four different range types. By default, the dialog selects the range with the larger values representing good (red – yellow- green). However, the range immediately below the default selection indicates that smaller values such as in golf are better (green-yellow-red). I can also define KPIs in which central values are good or central values are bad. These are represented by the sequence (red-yellow-green-yellow-red) and (green-yellow-red-yellow-green) respectively in the figure.
Beneath the definition of the status thresholds, I can select the display icon style. The first seven styles have only three icons and should be used with the three area definitions while the three sets of five icons should be used with the five area definitions. To make a selection, just click on the style.
There is also a section in the form for defining descriptions that can be expanded or collapsed as needed. These definitions are optional and for now, I will skip over them.
After defining your KPI properties and clicking OK, you should see your calculated measure has a small green-yellow-red bar icon to the right as shown in the following figure to indicate that this measure defines a KPI.
I am now ready to build my pivot table using my new KPI calculations. If I did not already have pivot table, I would begin by selecting PivotTable from the Home ribbon to create a pivot table. If the pivot table already exists (as it does in my case), I can simply switch to the Excel pivot table sheet. The PowerPivot Field List shows the message that the data has changed and prompts me to refresh which of course I will. The PowerPivot Field List should now show my calculated measures in the table where I defined them, FactSales, in this example. The following figure shows this updated Field List.
After selecting various columns for my Values, Row Labels, Column Labels and Slicer, I see data like in the following figure.
Note in this figure, I chose to display my three new calculated measures related to product sales quantity. I am displaying the quantity sold in the current time period, the previous quarter and the growth as a percentage as well as the KPI indicator. Of course you could just show the grow rate and KPI indicate if that is all you or management really wanted to see.
Because I want to see quantities by product, I select my product hierarchy that I created in last week’s blog as my row dimension. Note that by using the product hierarchy, I can start by looking at sales quantities across an entire category and then drill down to see quantities sold by subcategory and even by product. The pivot table automatically calculates the KPI at each hierarchy level making it easy to discover which products in the category contributed to the good or bad performance of that category. (Similarly, I could define KPI values to calculate quantities or sales by store and region to determine which stores are doing well and which stores I might want to close.)
Oh, by the way, the trick, so to speak, to get the correct current period sales quantity was to use the field CalendarQuarter as a slicer for the pivot table and then selecting only a single quarter. Therefore the current product quantity represents the sales for the selected quarter and the LastQtrQty represents the sales quantity for the product for the previous quarter. If I wanted to compare sales quantity year by year, I would have CalendarYear as my slicer and defined my last period measure something like:
LastYrQty := CALCULATE([CurrentProductQty], dateadd(DimDate[DateKey], -1, year)
I’ll bet you can probably guess how to create a measure to compare product sales by month.
C’ya next time for more PowerPivot 2012 fun.