In previous weeks, I explained how to build a Tabular model that in many ways produces the same results as some of my previous blog entries in which I used PowerPivot for Excel. This week I continue by showing how to create KPIs within the Tabular model.
Let’s assume that the management of Contoso is interested in looking at the change in sales from one month to the next specifically within their ‘stores’ channel. To do that, I will begin by creating a new measure that calculates store sales using the CALCULATE() function. The advantage of nesting the SUM() function (which you might have thought of using) inside the CALCULATE() function is that CALCULATE() enhances the function used as the first parameter by adding a second parameter that applies a filter to the data. In this case, I want to filter the data based on the channel type ‘Store’ found in the table Chanel and field ChannelName. The following figure shows the expression used to define the measure StoreSales.
If my intent is to show sales by month in my pivot table, then I need to create a second measure that calculates the sales from the previous month. While the first expression did not reference a time period like month in the filter, I will actually apply a filter of month to my pivot table by making month the row dimension. Unfortunately, this does not automatically translate into calculating data from a prior month without some additional DAX help. However, I can use the CALCULATE() function again applying it to the StoreSales measure I just calculated and applying a filter to get only the previous month’s data by using the DATEADD() function as shown in the following figure.
Finally, given the sales for the current month and the previous month, I can calculate the growth in sales. Rather than display growth as a dollar amount, it makes much more sense to see growth as a percent change from the previous period. In other words, if sales were hypothetically $200 in one month and $208 in the next month, I would have a 4% growth as calculated by the expression:
(208 – 200) / 200 * 100 = 4%
The following figure shows the corresponding DAX expression to calculate the sales growth measure.
If you look at the Measure grid, you can see these three new measures. By default, the calculated values are displayed as decimal numbers. I could place these measures as they are on a pivot table (and I will as you will see later) and then format the numbers as currency or percentages. However, each time I use these measures, I would have to format the values again. If instead, I define the format property of each of these measures in my model definition, I will not need to format them each time I use them. To do this, click on each measure one at a time and modify the Format property. In the first figure below for the measure StoreSales, I define the value as Currency. This format option displays a currency symbol at the left with commas separating thousands and two decimal places.
Similarly, I can format the LastPeriodSales measure as Currency and SalesGrowth as Percentage.
Next, before I apply these measures to my pivot table, I want to add a slicer to the table to only look at data by year. Slicers are available from the Analyze ribbon in the PowerPivot Tools group of Excel specifically in the Filter group.
From the Insert Slicer dialog, open the table containing the dimension you want to use in the slicer and then click in the checkbox before the name of the field you want to use. In this case, I want to use CalendarYear as my slicer as shown in the next figure.
With my slicer added to the pivot table, I add my three measures to the values area. By choosing a year in the CalendarYear slicer, I can see the store sales for the year 2008. The Last Period Sales (assuming that I use the previous month calculation) shows me a slighted strange time period of December 2007 to November 2008. Finally, the Sales Growth shows the percent growth (or shrinkage) for these time periods. Now before you say anything, I still want to change the row dimension that I’m using to be Years, then Months. But I’ll do that later.
First, let’s define the KPI for the SalesGrowth measure. Returning back to SSAS and the model.bim tab, I right click on the SalesGrowth measure to display the popup menu shown below. The option from this menu that I want to use is Create KPI.
The Key Performance Indicator dialog that appears next begins by having us define the target value. KPIs always compare a measure to a desired value. Sometimes that value needs to be another measure (perhaps you want a certain market penetration based on the total population of the areas where you have stores). Sometimes that value can be compared to an absolute value. In this case, I want to compare the value to 0.0% which represents no growth (or loss) in sales. Numbers greater than zero indicate growth. Numbers less than zero indicate losses in sales.
Next, I can select whether I want to use a three or five band scale. If I select a three band scale, the typical use is to indicate that high values are good or low values are good. Five band scales on the other hand generally are used to indicate the middle values are good or extreme values are good. In the target bar, the dialog shows a small text box above the boundary of each color change. In this text box, I can enter a value to indicate when the status of the KPI should visually change. For example, in the following figure, I entered a lower value of -0.1 and an upper value of 0.1. These two values a decimal numbers correspond to -10% and 10% respectively. If I wanted to my band to indicate deviations of 5% from the central value of 0.0, I would enter the lower value of -0.05 and an upper value of 0.05.
After selecting the number of bands and their order, I can select from a variety of icon styles to display at a glance the status of the KPI.
After finishing the definition of the KPI by clicking OK, I can refresh my pivot table in Excel to display the KPIs group as shown in the following figure. Each KPI that I define is listed under the group KPIs. By expanding the individual KPI values, I can get access to the value, goal, status, and trend. In this case, the value is the percent growth of sales. The goal is 0% and is probably not something I would display in this case. However, the Status value will display the icon selected the KPI dialog I shown above.
The following figure shows my revised pivot table. However, it still shows the product hierarchy as my rows.
Changing the product hierarchy to CalendarYear and then CalendarMonth, my pivot table begins to make more sense. In fact, you can see where the store sales for any given month in the year displayed (2007 in the following figure) is the same as the value for the field Last Period Sales in the subsequent month. In other words, June Store Sales are the same as July’s Last Period Sales. This is exactly what I want. Only a couple of problems are left. First, the months are listed alphabetically rather than chronologically. Second, the columns where there is no data for a previous month such as January of 2007 display an error (#NUM!) for the Sales Growth but displays a green status indicator.
Next time, I show you how to clean up these minor problems as I finish out this series.