(This article expects from the reader basic experience with PowerPivot. You should know how to access a PowerPivot table from Excel and how to turn it into a pivot table or a chart.)

KPIs for a grocery store

I think this topic is introduced best using an example. Let’s assume an offline store and we are responsible for the data analysis. The boss wants us to count the people visiting the shop considering their age group and also how many of those visits lead to a purchase.

Let’s introduce a few definitions

A is an age group from the set ’16 to 19′, ’20 to 29′, ’30 to 39′ and ’40 or older’.

N(D,A) is the number of visits in the shop at date D by people aged A

P(D,A) is the number of visits that resulted in a purchase at date D by people aged A

Now we can define a purchase probability X(D,A) observed for a time span (a single day or several days) and a specific age group. f.x.:

So if on May 3rd 2012 31 visits by people aged 20 to 30 years were counted and 26 of them resulted in a purchase then we can deduce that the purchase probability at that day was 84%. This information for all age groups and a longer time period might help to improve the shopping experience for the customers.

Let’s assume we are fetching the data for N and P from a warehouse database into a PowerPivot-table named shop.

The shop manager wants a report

Of course the shop manager wants the KPI X(D,A) reported using charts. And this chart should contain all four age group specific ratios AND the ratio for all age groups together. This is not possible with basic pivot table settings because we would need to apply a categorization (“column labels”) for the age groups. And if we don’t apply a separation (“column lables”) then we end up only with the ratio for all age groups combined.

The solution I am going to describe you here is to introduce a measure for all four age groups and one for the ratio combining all age groups. I am not sure if this is the best solution and it won’t be very convenient anymore if we are distinguishing between too many categories (age groups here) – because we would have to introduce a new measure for all of them. But it does the trick and it is also a good use case to show how CALCULATE() is applied.

Defining new PowerPivot measures using the DAX language

Measures are defined using a language called DAX which (only) resembles usual Excel-formulas and was introduced by Microsoft specifically for this purpose along with PowerPivot. If you want to define a new measure you open the PowerPivot field list and right click on the name of the table for which you want to introduce a measure – “shop” in this case. In the context menu you’ll find an option for addition of measures. The next thing you’ll see is a dialog for entering the DAX formula.

The DAX code for X(D, ’16 to 19′) could look something like this:

CALCULATE() takes two type of arguments. The first one is declaring the value to be used and all arguments after that first argument are defining the filters to be apply. Put into words the expression in the numerator of the quotient would mean this:

“CALCULATE the value of the SUM of the field visit_and_purchase from the PowerPivot table shop where the associated age group is ’16 to 19′.”

We need SUM() here because we maybe don’t want to see this ratio on a daily but on a weekly basis and then Excel would have to add the seven values up.

The DAX expression for X(D, { ’16 to 19′,’20 to 29′,’30 to 39′,’40 or older’ }) could be this:

The logic behind it is basically the same as above. But this time we use ALL() to tell Excel to not restrict the age group. That we are referring to the age group we specify by passing the field reference for age group.

Now we can chart these ratios by dragging the five measures into the value section of the PowerPivot field list.

I hope this was helpful to you. If not you are welcome to ask me for clarification.