Latest Enterprise DNA Initiatives

Power BI Challenge 17 - Environmental Data Reporting

All,

I am pinch-hitting for @haroonali1000, who is under the weather this week.


Data Challenge Participants,

OK, we’ve got a really interesting one for you this month. This is the first time we’ve done a challenge based on environmental data, and the information provided is real, unmasked data that is currently being studied to address real-world environmental problems. Thus, in addition to honing your Power BI skills, you actually have the ability in this challenge to potentially influence the manner in which this data is visualized and reported.

For those of you who’ve been looking for a way to build a portfolio to advance your career or elevate your profile, there’s no better way to do so than within this challenge, where you can get feedback from the community, eDNA experts and Sam McKay, and in the end produce a complete reporting solution that addresses real-world, important issues.

THE BRIEF

You are a data analyst and visualization expert at a large environmental organization in the United States Mid-Atlantic region representing Delaware, District of Columbia/Washington DC, Maryland, Pennsylvania, New York, New Jersey, Virginia, and West Virginia. You are tasked with developing a report to communicate relevant environmental data to help visualize growing patterns. The indicators have been selected and data have been gathered. It’s your job to determine the best way to visualize the data and develop a reporting solution to communicate the key information.

What are the indicators?

An indicator is a numerical value derived from actual measurements of a stressor, state, exposure, or human health or ecological condition over a specified geographic area, whose trends over time represent or draw attention to underlying trends in the condition of the environment. The indicators are independent and for the purposes of visualization, no correlation or causation is to be connected or alluded to.

The report should include information on all four environmental indicators that are important to the region.

  • Heavy Precipitation: yearly summary of daily rainfall from 23 sites (22 airport, 1 non-airport location) throughout the region.
  • Ambient Air Toxics: concentration of air toxics that are harmful to human and ecological health at sites in the region. Note that concentrations are expressed in µg/m3 (micrograms per cubic meter of air).
  • Asthma Prevalence: prevalence of asthma in adults (through modeling) by census tract level and prevalence in children by state percentage throughout the region. Note that in the children’s asthma data, “suppressed” means that the number is too low to show without potentially exposing someone’s identity and “unstable” (usually overlapping with “suppressed”) means that the numbers used to calculate the rate are so low that they’re statistically unreliable.
  • Human Exposure to Contamination: contaminated sites within the region that have human exposure concerns. There are only three categories of sites: Potential for Concern, No Concern for Exposure and Insufficient Data.

Who are the users?

This report will be used by three types of end users with different goals:

  • Regional Data Scientists who will use the report as a starting point for more comprehensive assessment of environmental trends
  • Upper-Level Managers who are not familiar with the details of the datasets, but will use the report to track major environmental trends and will use the visualizations generated to communicate this information in external presentations
  • Policy Makers who need to understand the data at a glance to support decisions about how to allocate funds to the aspects of their region that need the most attention

Is there anything else to consider?

  • Entries are not limited by number of pages or technique. Feel free to use all the techniques at your disposal – tooltips, drill throughs, page navigation, etc.
  • However, the front page of the report should be a summary dashboard, capturing what you think is the most important information for each of the four indicators. Having a plain language summary of findings as a component of this would definitely be viewed as a plus.
  • The biggest challenges here will likely be identifying key trends in each of the indicators, and figuring out how best to clearly present a large volume of data spanning multiple locations and decades.
  • Be mindful of accessibility issues (e.g., color schemes should be readable by color blind users)
  • You may want to play with different population scales (census tract, county, state, etc.) that best fits the data available.
  • Feel free to add in demographic data to support the visualization. You can find demographic data from the United States Census Bureau.

Submission of entries

To be considered within the competition, entries are due no later than 11:59pm PST Sunday, December 12.

If you are not already following Enterprise DNA on LinkedIn please do so.

How to submit:

  • Email the completed PBIX file to powerbichallenge@enterprisedna.co
  • Take an image and the Publish to Web URL of your report and post it to the Enterprise DNA forum.
  • Take the image and URL and post it on LinkedIn tagging Enterprise DNA saying “I accepted the Enterprise DNA challenge.”
  • We always encourage all participants to do a writeup and share their experience of participating in the challenge and sharing it on the forum and on social media.
  • I f you need any help with publishing, please reach out to one of the team for assistance (post in the forum or email to brian.julius@enterprisedna.co).

Judging Categories

Overall Winner – all entrants are eligible

First Time Participant winner - open to any Enterprise DNA member who is taking part in the challenge for the first time.

Winning Non-member - open to entrants not currently eDNA members.

There are some excellent prizes on offer from free membership (for all category winners) and more, to having your work showcased across the Enterprise platform. So please do get involved and share this opportunity with others who might be interested.

As always, best of luck!

DC17FinalData.xlsx (1002.6 KB)

10 Likes

Hi Brian,

Any data dictionary, there are a few columns name that escape my mind…

Thanks and Regards,

F Pastor

1 Like

Hi Brian,

A couple of questions

  1. The ‘Human Exposure to Contamination’ worksheet has a column called ‘Fiscal Year’ while other worksheets use just ‘Year’. Is ‘Fiscal Year’ = ‘Year’ in the context of this data? If not how is ‘Fiscal Year’ defined?

  2. The ‘Adult Asthma’ data only covers 2018. Is that correct?

Regards
Antony

2 Likes

Hi @FPastor

Are there particular columns that you are not sure about?

Cheers
Antony

2 Likes

Thanks AntonyC,

Value is 3 tables - Adult and child Asthma and Air Toxic Concentration. Are these averages???

Regards,

F Pastor

1 Like

@AntonyC , @FPastor ,

Awesome! Great to see you guys already diving into the data. Also great to see some newcomers like @AntonyC and Paul Gerber jumping in, in addition to veterans like @FPastor.

Let me do my best to answer the questions posed already:

  1. yes, adult asthma data is for 2018 only
  2. fiscal year starts October 1, but since we don’t have specific dates tied to the observations, just assume that fiscal year is the same as year.
  3. for the adult asthma data, I believe the value represents the modeled/predicted percentage for the typical individual within that census tract. For the child asthma data, I know that is based on actual observations. That’s why there’s such concern about suppressing the information where there are few observations and thus could reveal the identity of specific individuals.
  4. For the air toxics data, I’m not sure whether the value represents a single measurement in time or an average over the period. I will check with the submitter and get back to you on that one as soon as possible.

– Brian

5 Likes

Thanks @BrianJ.

I was looking at the Precipitation data. I assume the column called: ‘Frequency in 0 inches’ is number of days where there was no rainfall for that year / site

2 Likes

@AntonyC ,

Yes, that’s correct.

  • Brian
3 Likes

Hey @FPastor ,

Can you please talk to your buddy @DavieJoe and let him know he doesn’t get to win one Challenge and then ride off into the sunset forever. :grinning_face_with_smiling_eyes:

– Brian

@DavieJoe - just couldn’t resist giving you a hard time. I know you’ve been really busy, but definitely do hope we’ll see you in this challenge.

4 Likes

image

Haha, but I’m still 1 for 1 at this time. I welcome you giving me a hard time and appreciate you thinking of me. Really interesting data challenge and something that I’m passionate about.

Don’t worry, I am planning on entering this challenge, got a bit more free time & headspace now.

I’ve already downloaded the dataset and plan on making a start this weekend when I’m in sunny Scotland, the fresh sea air up there will surely prove inspirational :wink:

Regards

DJ

image

2 Likes

@BrianJ,

Consider it done!!!

@DavieJoe,

Check your linkedIn private messaging :face_with_raised_eyebrow:

2 Likes

@DavieJoe,

Bring it on!!! or should I say catch me if you can!!! :sunglasses:

F Pastor

2 Likes

Haha thank you for the Linkedin private message of

“this and that and the other, blah blah try to participate in the EDNA Challenge!!!”

Consider me told!

3 Likes

@DavieJoe

OMG, you (deleted expletive content)

:joy:

F Pastor

2 Likes

Your description of me was quite accurate! :rofl:

2 Likes

@FPastor ,

I just spoke with the submitter, and for the air toxics data, the values reported are annual averages based on daily (and in some cases subdaily) observations.

Good question - thanks!

  • Brian
2 Likes

Thank you Brian.

F Pastor

1 Like

@DavieJoe you are very hopeful of seeing sun in Scotland :wink:

You might get some inspiration from the rain and cold. The joys of living on the mud island :grinning:

1 Like

@BrianJ - I’ve missed the last few challenges, but am definitely jumping into this one. I also like that this one has a deadline after the Thanksgiving holiday.

This means that instead of arguing with your brother-in-law over politics or sports (and yes - the Detroit Lions will lose their game again this year), you can instead debate over whether you should add a new column to the table or create a measure in DAX and perhaps increase overall performance.

2 Likes

@BrianJ

Can I know a bit more about the submitter;

  1. NGO or Private
  2. Do they have their own brand colour/font…
  3. Any other normal info you can provide

It will helps us a bit with theme, colour palette…

Thanks

F Pastor

1 Like