A Massachusetts General Hospital report and theme

I participated in this Maven Analytics challenge to deepen my knowledge of Power BI. As an experienced UX designer but a newcomer to data analytics, I'm fascinated by the idea of storytelling through data. I’m looking to expand my skill set in UX (and front-end) development by diving into the world of data analytics.

My goals for this challenge

• Learn to work with Power BI
• Learn to write DAX
• Answer the questions provided in the briefing
• Visualize relevant data into easy-to-understand insights
• Group data into smaller, clear stories
• Build a JSON theme based on the MGH style guide (interpreting massgeneral.org)

Assumptions

A major pitfall in data analysis is building a report based on assumptions. In a typical scenario, you would simply ask the client for clarification. However, in this case, I had to make decisions based on assumptions, and some questions remained unanswered.

I decided to work on a Quarter to Date basis. Initially, I considered a Month to Date approach, but since the last encounter entry was on February 5th, there wasn't enough data to make a month-based analysis interesting.

With the help of ChatGPT, I assumed that an admission could be defined by two encounter classes: Inpatient and Emergency. I classified all other encounters as visits. This distinction made the project more interesting for me as I had the opportunity to write more DAX measures.

The definitions of costs were unclear to me. Initially, I assumed that encounter costs would at least equal the sum of the procedure costs. However, this assumption was incorrect: the total procedure costs exceeded the total encounter costs. Without a clear understanding of this part of the data, I created an Encounter page using data from the encounter table and a Procedure page using data from the procedure table.

The fourth question, "How many procedures are covered by insurance?" cannot be answered with this dataset, because of the cost differences of encounters and procedures. Instead, I created a visual to show the relationship between encounter costs and the number of performed procedures.

Data transformation

There was a bit of data transformation necessary to improve data quality.

I added a dimension data table based on the start date of the Encounter table.

The SNOMED data was inconsistent. The codes and their descriptions should be unique, but they weren't. I manually corrected these issues and removed the information in brackets at the end of the descriptions, which contributed to the inconsistencies. Ideally, this data should come directly from SNOMED.

Some encounters, 89 to be precise, did not have total costs but did have base costs. From the data dictionary table, I understood that base costs are part of the total costs. My solution was to create a transformed cost column where those zero values were replaced by the base costs.

I encountered encoding issues in the patient table. Despite trying different encoding methods, I had to manually correct the strange characters. Although I didn't end up using that data, it provided a valuable learning opportunity.

Storytelling

How does user experience design play a role in data analytics?

User experience design focuses on the user's journey while interacting with a website, app, report, etc. A data report should present the most relevant information and be easy to understand and navigate. Bonus points if the data report adheres to the client's style guide.

I based the visual design on massgeneral.org, considering colors, shapes, and fonts. Since Power BI has limited design options, creativity is key. It's easy to overdo the available formatting options, which can lead to hard-to-navigate designs. I strive to keep my designs clean and organized. If you think my report looks simple, awesome! That means I achieved my goal!

I paid extra attention to detail in the KPI cards. When narrowing down results using slicers, I avoided displaying simple blank values like zeroes or dashes. Instead, I formatted my measures to provide more clarity when returning zeroes and blanks.

I divided the content into five main pages:

1. Overview page: This page is designed for the executive team, providing answers to the main questions.
2. (encounter) Costs and insurance information: This page focuses on encounter costs and related insurance information.
3. Encounters: This page distinguishes between admissions and visits, providing clear insights.
4. Procedures: I used a table visualization on this page to allow multiple sorting options, showcasing the most frequently performed procedures.
5. Patient profiling: This page delves into patient profiles. A side note: I'm currently facing issues with the Azure map not working when embedded. I plan to resolve this after posting the challenge.

Additionally, there's a secret page that can serve as a dictionary or provide more information about the report's content. Did you find it?

Improvements

There’s still a lot of room for improvement since this is my first solo Power BI project, and I'm aware that I may not be fully up to date on best practices, naming conventions, etc.

Areas for improvement (if given more time):

• Re-admission and re-encounter visuals: These visuals do not currently relate to the fact table, which is why the slicers have no effect on them.
• Drill-through for patient information: I initially wanted to implement a drill-through feature to explore patient information in more detail. However, due to time constraints and the scope of the project not focusing on patient data, I decided not to pursue this further.

Thank you, Maven, for providing this dataset and challenge incentive!