Regional view of Coronavirus in England 04-28-2020 02:34 AM - last edited 04-28-2020 02:37 AM

[M82]

I hope everyone is keeping safe and well during these turbulent times. Almost three weeks into lockdown in the UK, I have finally got around to finishing a Coronavirus dashboard for England in relation to Coronavirus cases reported. If time permits, I will extend this dashboard to the rest of UK.

The reason behind this dashboard was to demonstrate the visibility of the "Corona growth curve" at a regional/local authority level: a reminder of how important it is to stay at home to flatten the curve. We have all seen this curve (too) many times at national level, but I was very curious to see what was going on regionally.

The data source is Public Health England and I have pulled in some some census information (population/household data) to create some metrics such as "cases per 10,000 people". The census was, unfortunately, almost 9 years ago, so this stat should be taken more as a guideline than actual fact. Also, the main source of the data is Public Health England, which itself has some caveats as not all cases are reported or tested. There is also no access to information related to mortality or recovery rates at a regional/local authority level, with this information being reported at national level only.

Below are a few snippets from the report:

Main Page (with selection slicers along the left). Toggle between "Daily Heat Grid" or "Map" and hover over map for additional data points (Lambeth shown below)

The "Daily Heat Grid" (below) can be viewed at a daily or cumulative level. The dark red denotes an increase of more than 500% from the previous day:

Comparison by region (with County and Local authority also included):

The report is fully interactive, allowing you to view the "curve" and compare it to any region/local authority of your choice. Unfortunately, Public Health England use a combination of Local Authority and County level information so some areas are not as transparent as others. Also puzzling, is the fact that Hackney is combined with the local authority "City of London".

One conclusion I can draw which is clear from the data, is that some regions should have been in lockdown earlier (Birmingham/Hampshire being prime examples).

Data Sources:

https://fingertips.phe.org.uk/documents/Historic%20COVID-19%20Dashboard%20Data.xlsx

https://www.ons.gov.uk/census/2011census

eyJrIjoiMTg1NmQ3N2EtZWFkMS00ODg1LWE5ZWYtMmMwNzIzYmQyZGIwIiwidCI6ImFlMjNjNWFlLTMzYTMtNGYzMS1hMDkzLWJhNzY1OWM1MzBlMSJ9

[Mallory24]

Hi M82, 

This is a great dashboard! I agree with africat, if you could share the pbix file it would be very helpful.

I have a few questions re the data sources  and I was hoping you might be able to help: Are you still using the dashboard? Do you have a data feed automated? I am trying to build something similar and I would like to have an automated connection through Power BI. The datasets (while still collected by PHE) for cases and deaths have now been moved here: https://coronavirus.data.gov.uk/#local-authorities  and I have been trying to use the Web connector in Power BI, but I am getting this error: "DataSource.Error: Web.Contents failed to get contents from 'https://coronavirus.data.gov.uk/downloads/csv/coronavirus-cases_latest.csv' (308): Permanent Redirect".

So I was curious, what dataset are you using, is the connection automated or not and if it is, did you have this problem and how did you deal with it?

Thanks,

Mal

[M82]

Hi @Mallory24 ,

I did manage to do a web connection on the original dataset (https://fingertips.phe.org.uk/documents/Historic%20COVID-19%20Dashboard%20Data.xlsx) and it was fully automated and refresh was working perfectly. The dataset was was cumulative data only, so I used some DAX to calculate daily cases. Used functions like PREVIOUSDAY and DATEADD to achieve this.

However since 14th April they are using a CSV file and I could not connect for the same reasons you have listed.

So now I am downloading the file to refresh manually - it takes a few extra clicks so not too time consuming.

@africat I will add the new pbix file shortly.

M82

[africat]

Hi M82

Thanks very much for uploading the pbix.

Have you thought about adding a shape map of the NHS regions to the dashboard?

Cheers

[M82]

Not been thinking about NHS region as it's quite difficult to know the boundaries to generate a shape map for it. The govt website you'll notice uses bubble maps too, I would assume due to a similar reason.

[africat]

Take a look at the below website. Loads of different shape maps for the UK.

http://geoportal.statistics.gov.uk/

I have attached the NHS England Region json that you could use. I am in the process of amalgamating the attached file with the UK country shape file to include Wales, Scotland and Northern Ireland

[Mallory24]

Hi @M82,

I've asked about this in this post: https://community.powerbi.com/t5/Power-Query/Web-Connector-error-308-Permanent-Redirect-issue/m-p/10...and it looks like it's just the web address that I had wrong. Have a look, perhaps is the same issue you are having - they just changed the lcoation of the file. 

Hopefully you won;t have to do this manually anymore 🙂

Mal

[africat]

Hi Mal

Thanks for the update. I was having similar issues as yourself.

Cheers

[africat]

Hi M82

I really like your dashboard. Easy to interpret and well laid out. Are you able to share the pbix file by any chance?

Cheers