KPI in Power Pivot Challenge 01

by | Jan 29, 2024 | Excel

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

Highlight weekends with conditional formatting

by | Dec 8, 2022 | Excel

Some­times, we want to high­light rows of week­ends. In such a case, we can use con­di­tion­al for­mat­ting but, first of all, we have to cre­ate a for­mu­la which will return TRUE for Sat­ur­day and Sun­day, i.e. week­end days. We can start with the WEEKDAY func­tion which will return the num­ber of days in a week (Fig.1).

High­light week­ends with con­di­tion­al formatting

=WEEKDAY(A2)

WEEKDAY function
Fig. 1 WEEKDAY function

We have our results. How­ev­er, this numer­a­tion assigns num­ber 7 to Sat­ur­day, and num­ber 1 to Sun­day. It’s not prop­er from our per­spec­tive. We have to mod­i­fy it by chang­ing the week day num­ber sequence. The best option we can choose is num­ber 1 for Mon­days and num­ber 7 for Sat­ur­days. That’s why we have to write 2 in the sec­ond WEEKDAY func­tion argu­ment (Fig.2).

=WEEKDAY(A2,2)

Selecting the correct option
Fig. 2 Select­ing the cor­rect option

And now, Sat­ur­day is 6 and Sun­day is 7. We can clear­ly see that week­ends are num­bers big­ger that 5 (Fig. 3). 

Weekend bigger that 5
Fig. 3 Week­end big­ger that 5

Now, we can sim­ply cre­ate a log­i­cal test that defines if the week day num­ber is greater than 5 (Fig. 4).

=WEEKDAY(A2,2)>5

A simple logical test
Fig. 4 A sim­ple log­i­cal test

When we copy our for­mu­la down, we can see that we have TRUE for Sat­ur­day and Sun­day, and FALSE for the rest of the days (Fig 5). 

TRUE and FALSE
Fig. 5 TRUE and FALSE

The results are prop­er, but we have to remem­ber that we want to high­light the whole row. We have to ask our­selves how our ref­er­ence should behave. We know that we will be copy­ing the for­mu­la down and to the right (hor­i­zon­tal­ly). What we have to remem­ber is that we always want to look at col­umn A, which is the Date col­umn. Even if we want to go one or two columns to the right, we always want to look at the cell from col­umn A (Fig. 6). 

A cell from column A
Fig. 6 A cell from col­umn A

That’s why, we have to lock the col­umn by press­ing the F4 key, but not the rows. It means that we have to put only one dol­lar sign (Fig. 7). 

=WEEKDAY($A2,2)>5

Locking the column
Fig. 7 Lock­ing the column

When we copy the for­mu­la down, noth­ing changes. But, when we copy it to the right and down, we can see that TRUEs and FALSEs are in a row (Fig. 8).

Rows
Fig. 8 Rows

Now, we have to copy our for­mu­la and the select the range on which we want to add con­di­tion­al for­mat­ting. The range starts with cell A2, so let’s press this cell, then Shift + →, two times to the right and Ctrl + Shift + ↓ to the end of our data. Cell A2 has to stay active and we will start cre­at­ing our for­mu­la from the per­spec­tive of this cell (Fig. 10).

Selected range with an active cell
Fig. 9 Select­ed range with an active cell

Now, we go to the Home tab, then to Con­di­tion­al For­mat­ting, and we select the New Rule option (Fig. 10). 

New Rule option
Fig. 10 New Rule option

In the win­dow that will appear, we have to select the Use a for­mu­la to deter­mine which cells to for­mat bar, then paste our for­mu­la into the Edit the Rule Descrip­tion and press the For­mat but­ton (Fig. 11). 

New Formatting Rule window
Fig. 11 New For­mat­ting Rule window

In the For­mat Cells win­dow, we press the Fill but­ton, then choose the col­or we like. Let’s choose a shade of grey, then the OK but­ton, and the OK but­ton once again in the New For­mat Rule win­dow (Fig. 12).

Fig. 12 For­mat Cells window

Now, we can see that each day of the week­end is high­light­ed the way we want­ed (Fig. 13). 

Fig. 13 High­light­ed weekends

https://www.youtube.com/watch?v=6jhZhEyk64A