Power BI Challenge 11 – Covid 19 Reporting

[EnterpriseDNA]

Hello All, I trust you are well and keeping safe.

It’s been a manic start to the new year with more lockdowns and restrictions being placed on many of us.

Last year we made some amazing strides with the Enterprise DNA Challenge and want to continue that momentum into this year.

We have some fantastic ideas and challenges lined up for the community and really cant wait to get going.

As always if you have any suggestions or comments, we are always happy to listen. Please feel free to reach out to myself or the Enterprise DNA team.

Check out the post below to learn about the challenge.

First time participant winner

Really simple, its open to all Enterprise DNA members. All you must do is let us know it’s your first challenge.

There is some amazing loot on offer not to mention the learning opportunity and free membership on offer.

We really want to see more of you get involved so even if it’s a post to the forum to join the conversation please do get involved.

To learn more about the challenge and how it works be sure to check out the forum below!

About the Challenges category - #2 by haroonali1000

If you’re not sure of anything just reach out to me.

Remember: The weekly winner will receive a complimentary membership to the platform that they can share with anyone and the opportunity for your work to be showcased across our channels.

The Brief

Right lets get to it!

So as you’re all aware there is currently a little something known as CV19 that is changing the face of the world as we know it.

Businesses and livelihoods are trying to cope as best as they can, so what is the current status of CV19 across the globe and for any selected country? Is it getting better, worse, do we need to take some action?

Well now hopefully you as an analyst can help!

There are some amazing resources available online covering the CV19 pandemic and none better than the John Hopkins University (JHU) reporting which is used across the globe to report cases, deaths, and recoveries. News outlets and business across the globe use the JHU data for their reporting.

See the link below to see the dashboard produced by the JHU team.

COVID-19 Map - Johns Hopkins Coronavirus Resource Center

A pretty awesome dashboard?

They haven’t only produced a great dashboard they have also made the data that sits behind it openly available.

So we are going to now use this data and create our very own dashboard in Power BI.

The key objectives are:

Connect to open-source data

Import and create a data model

Create a 1 page report of what the current situation is.

The JHU team have made the data available in GitHub and we are interested in the time series data available at the following link.

Within this data set there are three key files that we are interested in pulling in for our analysis. The files we are interested in are listed below. I have also placed the URL of the required CSV.

Confirmed - https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_confirmed_global.csv

Deaths - https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_deaths_global.csv

Recovered- https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_recovered_global.csv

The ask is that you pull this data in and build 1 page report covering what you believe to be the key statistics.

We are not looking for pages and pages of analysis think summary, C-level reporting a 1 page snapshot which lets us know what’s happening.

I have created a PBIX file where I have pulled this data in so nobody is starting from a blank canvas or completely lost. (wink, wink the Dimcountry dimension could require some further work and tidying).

Challenge 11 w ISO.pbix (872.2 KB)

I also did my first video for the challenges and went through how to pull the data in so be sure to check it out if you’re stuck. NOTE: In my video I did a merge which under the pressure of creating a video I got wrong I have since corrected it in the PBIX (Sorry).

Power BI Challenge 11 Announcement

Power BI Challenge 11 - COVID 19 Reporting

Right over to you guys, I hope I have been clear in the ask if not please do not hesitate to get in touch.

SUBMISSION DUE DATE - Tuesday, 26th January 2021 (PST)

Please can you all submit your PBIX files to powerbichallenge@enterprisedna.co

Best of luck!

Any issues or questions please reach out.

Haroon
Enterprise DNA

[haroonali1000]

Hi All,

There is also a great blog post available on this topic at: https://oexwp.azurewebsites.net/2020/04/19/utilising-power-bi-to-connect-to-open-data-sources-covid-19-reporting/

Thanks
Haroon

[jbressan]

Hi @haroonali1000, Can I used a different data source for this challenger?, I want to try this one
https://covid.ourworldindata.org

Jose Bressan

[emercado777]

HI Haroon,
Can I participate in this challenge? It would be my first time participating.
Thanks,
Emmanuel

[BrianJ]

@emercado777,

Absolutely! We encourage everyone to participate. I guarantee that regardless of where you are in your Power BI journey, you will learn a ton and have a great time in the process. Welcome to the Challenges!

• Brian

P.S. First-time Challenge entrants are also eligible for some great prizes:

Power BI Challenge 10- Supplier Insight

Just wanted to give a big to all the first time Challenge participants jumping in on this thread. Always great to have new entrants participating in the Challenge! Also, as @haroonali1000 mentioned, every Challenge we offer some great prizes to the first-time eDNA member entrant judged to have the top entry: a copy of the Definitive Guide to DAX, 2nd Edition (the indispensable "bible"of DAX) or your choice of any other book from the eDNA Forum Recommended List ; or a copy of SnagIt 202…

[BrianJ]

Challenge Participants,

For those interested, I have attached a modified version of Haroon’s PBIX file with the ISO 3 country codes added to the Country dimension table. This will make it much easier to build out your dimension table with additional country information if you choose to do so, since ISO 3 represents a consistent standard, while country name does not.

In addition, I added the ISO 3 code to the fact table, though you can easily hide or remove that with no impact if you’d like.

I hope this is helpful.

– Brian
Challenge 11 w ISO.pbix (818.7 KB)

[amira.bedhiafi.pro]

WATCH OUT FOR THIS ! Amira’s PBI Challenge 11 Story :
Behind every data there is a story. I called a friend of mine who is a doctor and explained to him the situation. I have data but I need to : Create a 1 page report of what the current situation is. Difficult right ?
He said : no problem ! count me in but I need your help also …
.
.
.
Guess what ? The quantity of blood in the donation center in my country Tunisia is decreasing. He asked me if I can donate. I didn’t hesitate. I went to the center and they gave me a notebook and a pen as a “Thank you”.

What I discovered after discussing the actual data with my friend, many governments give importance to the CFR, IFR and Case Fatality KPIs.

I tried to be simple as I can, since my friend advised me that the segmentation I used in my dashboard is a suitable one for his team.
But before jumping to the solution this is a picture of the pen and notebook I received with the draft of my discussion with my friend. I encourage you people to go and donate your blood because YOU DON’T HAVE TO BE A DOCTOR TO SAVE LIVES.

You can find my solution below :

Challenge 11.pbix (824.9 KB)

[Vasanth4949]

Sir this is going to be my first powerbi challenge. I am opting to participate in this

[haroonali1000]

Welcome onboard! I look forward to seeing your report!

[haroonali1000]

Awesome!

[haroonali1000]

Hi Jose,

While I’m not precious about the dataset I would prefer it we stuck to the chosen dataset for consistency purposes.

However feel free to use your chosen dataset if you feel there is a greater benefit to your personal development.

Thanks,
Haroon

[emercado777]

Excellent, I am looking forward to it. I am so excited

[tweinzapfel]

Everyone,

In looking at the Recovered data - it looks as if there is an issue around the 12/13/2020 time frame. From what I understand, the “value” column in the data represents the cumulative number of people who have recovered by each date. (Same for the confirmed and deaths data). However, for a number of locations (USA, GBR, FRA and a host of others) - the “recovered” amount goes from a certain value on 12/12/2020 - but then switches over to 0 on 12/13/2020. I’m seeing this both when I pull in the data directly or use one of the files provided above. And, it only occurs for some of the locations. While I initially thought this might just be missing data - it wouldn’t make sense for this type of dataset.

Just wondering if anyone else has seen this.

Thanks

[haroonali1000]

Hi All,

Apologies there seems to have been an error while uploading the PBIX file. The issue has now been fixed.

The link has been updated in the post and can also be found below. It includes the adjustment made by Brian in his earlier post.

Thanks,
Haroon
Challenge 11 w ISO.pbix (872.2 KB)

[haroonali1000]

Hi All,

Just to clarify that the CV19Confirmed table under the JHUData category is an appended query. If you just wanted to see the three source queries (maybe create a new fact) you would just need to remove the append or filter.

Sorry for any confusion - If you’re using the CV19 Fact table as provided there is nothing to worry about, and no need to make any change

There are some missing rows in the CV19Recovered, I had a quick look on GitHub and there are some comments but nothing indicating why so if anyone does find this out please shed some light.

Thanks,
Haroon

[jbressan]

Hi @haroonali1000 this is my one page report for this Challenger.

Online Version

[haroonali1000]

Awesome work @jbressan smart use of tooltips and the little switch between map and and the charts is nice.

Haroon

[haroonali1000]

Hi All,

Given some of the challenges with this dataset we have decided to extend the deadline by 2 days!!!

So the revised submission date is Thursday 28th 2021 (PST)

Have a good weekend.

Thanks,

Haroon

[chris786]

Hi All,
Something I’m not getting right. When I find the confirmed cases, it shows as 8,597,192,562.
Surely it cannot be 8 Billion. Something I’m missing here. Below is my formula.
Can anyone help.

Totals = SUM(CV19Fact[Value])
CALCULATE([Totals],FILTER(CV19Fact,CV19Fact[Status]=“Confirmed”))

Thanks,
Mustafa

[alexbadiu]

@chris786, the data shows cumulative values for each date. You should search for the highest value with the Status confirmed, not the sum. Good luck!