Today, we are going to address our first challenge. The challenge is to create KPI in the Power Pivot basing on an average blood pressure. We want to calculate our averages basing on weeks, as it’s more complicated that calculating monthly averages :).
For individuals — determine the average monthly ( for ambitious weekly) upper blood pressure KPIs. We assume that if it is in the range of 110 — 130 it is Good, if it is in the range of 100 — 110 or 130 ‑140 it is OK, and if it is above or below these ranges, it is Bad.
We have our Pivot with one cell selected. Let’s go to the Power Pivot tab (1), then to the Add to Data Model command (2) (Fig. 1)

We can change here our date format into the one without time (Fig. 2)

Now, we have to create an average basing on the Upper blood pressure column. We basically select one cell in the calculation area (1), then go to the Home tab (2), then choose the Average command (3) (Fig. 3)

The Power Pivot did the calculation automatically for us. I’ll just change the name so that it’s shorter. Let’s leave only ‘pressure’. Now, let’s change the format into Decimal Number (Fig. 4)

When we have the average with two decimal places, we can start creating a week column, because it’s hard to group by weeks in the Power Pivot. Let’s call this column WeekNum. Then we can use exactly the same function, which is the WEEKNUM function basing on the Date column. If we want our week to start on Monday, we have to write 2 (Fig. 5)
=WEEKNUM([Date],2)

Let’s press Enter, and we have our column with week numbers. All weeks start from Mondays. For some people this solution would be enough. However, I prefer to have the start day and the end day of the week. Let’s create another column. This time I’ll call it weekText. Let’s write ‘Date’. Then, we have to subtract the number of the weekday. To do so, we can use the WEEKDAY function again basing on the Date column. Now, we have three options of numbering the weekdays. 1- Sunday=1 through Saturday=7, 2 — Monday=1 through Sunday=7, 3 — Monday=0 through Sunday=7. The last option is the one we need. Monday will be the first day, but it has got zero. It means that we will start counting from zero which is exactly what we need (Fig. 6)
=WEEKDAY([Date],3)

After pressing Enter, we have our new column with only Mondays. In all seven rows of the week we have only Mondays. Now, let’s add the last day of the week. Let’s add a minus sign with spaces as a delimiter. It allows us to delete the time from our column. Then, let’s copy our WEEKDAY formula and paste it after the delimiter. Then, we need to write +6 days to have Sunday (Fig. 7)

We can see the results. If the date format is proper for us, this is all. But, if we want to change the date format, we should add the FORMAT function to the first and second calculation (Fig. 8)

However, the current form is OK for me, so we can create a Pivot Table based on our measures and calculations. Let’s click on the Pivot Table command in the Home tab (Fig. 9)

Let’s select the New Worksheet radio button (Fig. 10)

If I start with the Date column and go to Rows, most newer versions will group data automatically by months. Let’s try to group it by days (Fig. 11)

We can see that in the Power Pivot the number of days is grayed out and we cannot use it (Fig. 12)

That’s why we should crate additional columns to address this problem. I should use the weekNum or the weekText column in our Rows area. Let’s also add Person to the Columns area and the Grand total by going to the Design tab and clicking on the Grand Totals command. Let also add our measure, which is the fx pressure into Values (Fig. 13)

Now, we can see average blood pressure for each person and each week. We also have the grand total for all people in a week. It’s time to add KPI to our pivot table. Let’s go to the Power Pivot tab (1), then to the KPIs command (2) and choose the New KPI option (3) (Fig. 14)

In the Key Performance Indicator (KPI) window, in the KPI base field value, we have only pressure to choose from, so let’s leave it. In the Absolute Value bar let’s write 120. In the Define Status Threshold graphics we should write our assumptions, then select an icon style (Fig. 15)

Now, my measures have changed into KPIs (Fig. 16)

We still don’t see any icons. To switch them on, we need to uncheck the Status checkbox and check it again (Fig. 17)

Now, we can change the Pressure Status header name into KPI. We can also align the icons using the Center option in the Alignment area (Fig. 18)

We can see our average weekly measure and KPIs corresponding to that measure represented by icons.