Today, we are going to address our first chal­lenge. The chal­lenge is to cre­ate KPI in the Pow­er Piv­ot bas­ing on an aver­age blood pres­sure. We want to cal­cu­late our aver­ages bas­ing on weeks, as it’s more com­pli­cat­ed that cal­cu­lat­ing month­ly averages :).

For indi­vid­u­als — deter­mine the aver­age month­ly ( for ambi­tious week­ly) upper blood pres­sure 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. 

KPI in Pow­er Piv­ot | Chal­lenge 01

We have our Piv­ot with one cell select­ed. Let’s go to the Pow­er Piv­ot tab (1), then to the Add to Data Mod­el com­mand (2) (Fig. 1)

Add do Data Model command
Fig. 1 Add do Data Mod­el command

We can change here our date for­mat into the one with­out time (Fig. 2)

 Changing the Date format
Fig. 2 Chang­ing the Date format

Now, we have to cre­ate an aver­age bas­ing on the Upper blood pres­sure col­umn. We basi­cal­ly select one cell in the cal­cu­la­tion area (1), then go to the Home tab (2), then choose the Aver­age com­mand (3) (Fig. 3)

Average command
Fig. 3 Aver­age command

The Pow­er Piv­ot did the cal­cu­la­tion auto­mat­i­cal­ly for us. I’ll just change the name so that it’s short­er. Let’s leave only ‘pres­sure’. Now, let’s change the for­mat into Dec­i­mal Num­ber (Fig. 4)

 Decimal number command
Fig. 4 Dec­i­mal num­ber command

When we have the aver­age with two dec­i­mal places, we can start cre­at­ing a week col­umn, because it’s hard to group by weeks in the Pow­er Piv­ot. Let’s call this col­umn Week­Num. Then we can use exact­ly the same func­tion, which is the WEEKNUM func­tion bas­ing on the Date col­umn. If we want our week to start on Mon­day, we have to write 2 (Fig. 5)

=WEEKNUM([Date],2)

Creating a WeekNum column
Fig. 5 Cre­at­ing a Week­Num column

Let’s press Enter, and we have our col­umn with week num­bers. All weeks start from Mon­days. For some peo­ple this solu­tion would be enough. How­ev­er, I pre­fer to have the start day and the end day of the week. Let’s cre­ate anoth­er col­umn. This time I’ll call it week­Text. Let’s write ‘Date’. Then, we have to sub­tract the num­ber of the week­day. To do so, we can use the WEEKDAY func­tion again bas­ing on the Date col­umn. Now, we have three options of num­ber­ing the week­days. 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. Mon­day will be the first day, but it has got zero. It means that we will start count­ing from zero which is exact­ly what we need (Fig. 6)

=WEEKDAY([Date],3)

Creating a weekText column
Fig. 6 Cre­at­ing a week­Text column

After press­ing Enter, we have our new col­umn with only Mon­days. In all sev­en rows of the week we have only Mon­days. Now, let’s add the last day of the week. Let’s add a minus sign with spaces as a delim­iter. It allows us to delete the time from our col­umn. Then, let’s copy our WEEKDAY for­mu­la and paste it after the delim­iter. Then, we need to write +6 days to have Sun­day (Fig. 7)

First and last days of the week
Fig. 7 First and last days of the week

We can see the results. If the date for­mat is prop­er for us, this is all. But, if we want to change the date for­mat, we should add the FORMAT func­tion to the first and sec­ond cal­cu­la­tion (Fig. 8)

Adding the FORMAT function
Fig. 8 Adding the FORMAT function

How­ev­er, the cur­rent form is OK for me, so we can cre­ate a Piv­ot Table based on our mea­sures and cal­cu­la­tions. Let’s click on the Piv­ot Table com­mand in the Home tab (Fig. 9)

 Creating a pivot table
Fig. 9 Cre­at­ing a piv­ot table

Let’s select the New Work­sheet radio but­ton (Fig. 10)

Create PivotTable window
Fig. 10 Cre­ate Piv­ot­Table window

If I start with the Date col­umn and go to Rows, most new­er ver­sions will group data auto­mat­i­cal­ly by months. Let’s try to group it by days (Fig. 11)

Group option
Fig. 11 Group option

We can see that in the Pow­er Piv­ot the num­ber of days is grayed out and we can­not use it (Fig. 12)

Days grayed out
Fig. 12 Days grayed out

That’s why we should crate addi­tion­al columns to address this prob­lem. I should use the week­Num or the week­Text col­umn in our Rows area. Let’s also add Per­son to the Columns area and the Grand total by going to the Design tab and click­ing on the Grand Totals com­mand. Let also add our mea­sure, which is the fx pres­sure into Val­ues (Fig. 13)

Creating additional columns
Fig. 13 Cre­at­ing addi­tion­al columns

Now, we can see aver­age blood pres­sure for each per­son and each week. We also have the grand total for all peo­ple in a week. It’s time to add KPI to our piv­ot table. Let’s go to the Pow­er Piv­ot tab (1), then to the KPIs com­mand (2) and choose the New KPI option (3) (Fig. 14)

Creating KPI
Fig. 14 Cre­at­ing KPI

In the Key Per­for­mance Indi­ca­tor (KPI) win­dow, in the KPI base field val­ue, we have only pres­sure to choose from, so let’s leave it. In the Absolute Val­ue bar let’s write 120. In the Define Sta­tus Thresh­old graph­ics we should write our assump­tions, then select an icon style (Fig. 15)

Key Performance Indicator (KPI) window
Fig. 15 Key Per­for­mance Indi­ca­tor (KPI) window

Now, my mea­sures have changed into KPIs (Fig. 16)

KPIs
Fig. 16 KPIs

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

 Switching on icons
Fig. 17 Switch­ing on icons

Now, we can change the Pres­sure Sta­tus head­er name into KPI. We can also align the icons using the Cen­ter option in the Align­ment area (Fig. 18)

Center option
Fig. 18 Cen­ter option

We can see our aver­age week­ly mea­sure and KPIs cor­re­spond­ing to that mea­sure rep­re­sent­ed by icons. 

https://www.youtube.com/watch?v=lsyi8XEw2y0&t=3s