How to create your own Vaccine Tracker

Introduction

As vaccine roll outs start to pick up in Canada, we wanted to better visualize the percent of people vaccinated at both a national and province/territory level. Additionally, we wanted to break this down by the number of vaccines administered by different type (Pfizer, Moderna, and Astrazeneca). To do this, we analyzed data from a variety of sources to paint a user friendly and intuitive dashboard of the vaccine rollout.

Data Sources

The data sources used here are as follows. To link these, copy the URLs listed below and enter them into the “web” option in PowerBI.

1. Data by Type of Vaccine: From the Government of Canada Health Infobase website

· This is updated Canada wide every week

· 2 data sheets from this site are used, the High level Vaccination Data and the Vaccination Coverage by Vaccine Type

· Link to enter into PowerBI (Vaccination Coverage by Vaccine Type): https://health-infobase.canada.ca/src/data/covidLive/vaccination-coverage-byVaccineType.csv

· Link to enter into PowerBI (High Level Vaccination Data): https://health-infobase.canada.ca/src/data/covidLive/vaccination-coverage-map.csv

2. Vaccine Administration by province: From the COVID19tracker.ca website

· Link to enter into PowerBI: https://docs.google.com/spreadsheets/d/1PjkemMdFSZgA-M8Esr6rbNjHiyfcXcBxPeMjselJIso/export

3. Statistics Canada’s real-time Population Clock Data

· Link to enter into PowerBI: https://www150.statcan.gc.ca/n1/pub/71-607-x/71-607-x2018005-eng.htm

Creating your Key Visuals

Main Vaccine Map

To show a visual representation for the number of vaccinations per product type, a hierarchy uses the number of people administered at least one dose [numtotal_atleast1dose] and the product name [product_name]. This data can be found in the Health Infobase’s vaccination Coverage by Vaccine Type data

To create this, drag the [product_name] field onto the [numtotal_atleast1dose] field.

Add these values to a “Map” visual to create the following view.

Here are where the fields are placed into the visual:

Notes: Because this data shows information for all of the provinces/territories and Canada as a whole, you need to filter out the “Canada” value on the map:

Because this data also shows historical data, you don’t want to add all the previous vaccination numbers as a running total. To do this, you need to filter the data so it only shows the most recent data. This is done as follows, and can be done for all visuals that need to be filtered in this way.

Percent Vaccinated (Canada Wide)

To show the percent of people vaccinated, the “proptotal_atleast1dose” field is used. This provides the percentage of people who have been provided at least 1 vaccine dose.

To ensure that this only shows data at a Canada wide level, make sure that all the provinces are unchecked in the filter view.

This visual is created by overlaying a bar graph on top of a vector image of a syringe

Vaccinations in last 7 days

This uses data from the COVID19tracker.ca, which in turns pulls from each of the provincial COVID19 reports.

The [Change_Vaccinations] field is used on a line chart, and a relative date filter is used to limit the data to the last 7 days.

Percent Vaccinated (By Province)

This allows us to find the percent of vaccinations provided per vaccine type in any given province.

This is done by using the [Prop_atleast1dose] column from the Health Infobase’s vaccination Coverage by Vaccine Type data. To obtain a percent value, divide this column by 100, and then convert it to a percent.

New Vaccinations Per week (By Province and vaccine type)

One metric we were particularly interested in was finding the number of new vaccinations per week broken down by vaccine type.

The Health Infobase website only provides data of the Running Total of Vaccinations per type. To obtain the new number per vaccine type per week, you need to subtract the previous running total from the current running total.

The main challenge is that this data set contains values for the different provinces and the different vaccine types. Running a simple subtraction formula would result in values from 1 province or vaccine type being subtracted from another.

To avoid this, the columns need to be filtered in such a way that groups provinces and vaccine types together.

Steps

1. Go to the transform tab and sort the columns in the following order and direction:

· Province_&_Territories (Either ascending or descending)

· Product_Name (Either ascending or descending)

· Date (Descending, so that the newest values are on top)

2. Add a Index column (Also done in the transform tab) that starts from 1

3. Go to the table view, and create a new custom column to track the Increase Over Previous.

The formula used is:

The Blue part of the formula is used to subtract the running total of the [numtotal_atleast1dose] by the row below it (Providing the difference)

The IF formula at the start is there to check if this value is below 0. If it is below 0, then it knows that that is the start of a new province or vaccine type. Because all of the recorded vaccine values start close to 0 and the values are filtered in such a way (see step 1) that places the dates in descending order, whenever a line transitions to a new province or vaccine type, it will evaluate to a negative number.

· Each of these boxes show a transition from vaccine type (Pfizer to unknown in New Brunswick and Astrazeneca to Moderna in Newfoundland) or Province/Territory (New Brunswick to Newfoundland and Labrador)

· Subtracting these lines from the one below calculates a negative number (2606–22962, 19–4133, and 0–9762 all evaluate to less then 0)

If the value is negative, it looks at the next part of the equation :

Vaccination_Data_By_Product_Type[numtotal_atleast1dose]

This just pulls the value of that row, as any row before a transition represents the first recorded instance of vaccine administration for that respective vaccine type in a province/territory.

If the value is positive, the IF formula evaluates to FALSE and looks at the green part of the formula. This is the exact same formula as the first part of the formula, and returns the difference between that row and the one below it:

Credit goes to the PowerBI Forum community member Zubair_Muhammad for the formula that provides the difference between two rows. Link to the original answer can be found here.

Adding Images for Provinces

Using “Simple Image” visual, we can create a table of images for the provinces/territories. This takes the URL of a base64 image and displays it as a visual.

To do this, you can take any png/jpg image you want, re-size it to the proper dimension (You need to do this before processing your image), and then encode it into base64 format. This can be done at this website: https://www.base64-image.de/

Images of the province maps were screenshotted form this website: https://mapchart.net/canada.html

Once you do this, you can create a table in PowerBI with the provinces/territories in 1 column, and their respective images in another as follows:

After creating this table and adding the “simple image” custom visual, place the [Image URL] field into the visual.

Using Logos as filter buttons

To use the Vaccine logos instead of a filter, you can use bookmarks to achieve a similar effect.

Using the bookmark tab, you can show different parts of the dashboard depending on the bookmark selected. A good tutorial on how to do this can be found on the tessellation website here: https://tessellationtech.io/toggling-views-with-bookmarks-in-power-bi/#:~:text=Click%20on%20each%20of%20the,to%20show%20is%20being%20shown.