Creating Infographics with Power BI
Background
During the month of October at 3Cloud , we had a step challenge to encourage our team members to get more active with a little friendly competition. Seven years ago, I completely ruptured my achilles tendon, playing in an adult basketball league. My activity level dropped significantly after that injury, resulting in weight gain and overall declines in my health statistics. I used this competition as motivation to kickstart my fitness and health goals, leading to some pretty striking results in 1 month. This transformation felt like a great opportunity to get creative with 微软 Power BI and the Adobe Creative Suite to tell the story with an infographic-style report. As an added bonus, I got to use both sides of my brain with a little artistic creativity and data wrangling which I particularly enjoy. This article will provide information on how to recreate the Power BI report pictured below. All of the assets, including the Power BI, image, and data files will be located in a public GitHub repo referenced at the end of this article.
About the Data
I'm a Fitbit (now part of Google) user and my step activity and additional metrics were automatically tracked with their device and platform. I, also, manually entered daily weight readings using the accompanying iPhone application. To get the raw data, it was easy to export in their web application by navigating to the Settings (Gear Icon on the Fitbit Dashboard) and choosing the Data Export option. The direct link to this feature is Fitbit - Settings - Data - Export.
While using manual exports are great for personal exploration, it is recommended to leverage Azure SQL Database or Azure Synapse with data ingestion conducted through the Fitbit APIs to build this at scale.
After getting the data, I conducted a little data massaging to combine activity and weight data into a single tab in Excel. I did this mostly to simplify the Date dimension and will let Power BI handle that for me. I would usually create a shared Date dimension and keep the data segregated into different tables for a more in-depth design. Additionally, I added a Participants tab, to use as a page-level filter on the report and added a Participant Id column to the activity data. Even though the participant tab only contains a single record, it provides the ability to expand the dataset to multiple participants without future modifications to the report. With the data prep being complete, the next step involves importing the data into the report from the Home menu in Power BI and choosing the Excel option.
Data Modeling - Relationships, Measures, and Columns
After importing the dataset, we now need to modify the model by defining a relationship for the Participants to the daily Data and create some Measures to be used in the report visualizations. We only need to define a single relationship between the two tables, and this can be done by clicking the Model icon, on the left pane in Power BI Desktop. Next, you click and hold your left-mouse button on the Id column in the Participants table and drag it to the Participant Id field of the Data table. You will want to ensure the relationship is a one-to-many, in that, a single participant can have many records in the Data table.
Once we have the relationship established, we are ready to define some measures. All measures should be created in the Data table and the detailed formulas are listed below. To create a Measure, click the Report icon in the left pane of Power BI Desktop, and then right-click on the table you want to add a measure to. Choose the New measure option, and then copy the following measure and formula definitions into the Measure definition area. You will need to repeat this process for each of the 12 measures provided in the code snippet area provided. The last item we will add is a Column to the Participants table to store the participant's Full Name. Our Participants table stores first name and last name as separate attributes, and this column just concatenates those values with a comma to be used in a Filter later in the setup. Creating columns is the same process as measures, but you choose "New column" instead of "New measure" when right-clicking on the relevant table.
Starting BMI = VAR startDate = CALCULATE(FIRSTDATE(Data[Date])) RETURN CALCULATE(SUM(Data[BMI]), FILTER(Data, Data[Date] = startDate)
Starting Resting Heart Rate = VAR startDate = CALCULATE(FIRSTDATE(Data[Date])) RETURN CALCULATE(SUM(Data[Resting Heart Rate]), FILTER(Data, Data[Date] = startDate))
Starting Weight = VAR startDate = CALCULATE(FIRSTDATE(Data[Date])) RETURN CALCULATE(SUM(Data[Weight]), FILTER(Data, Data[Date] = startDate))
Ending BMI = VAR endDate = CALCULATE(LASTDATE(Data[Date])) RETURN CALCULATE(SUM(Data[BMI]), FILTER(Data, Data[Date] = endDate))
Ending Resting Heart Rate = VAR endDate = CALCULATE(LASTDATE(Data[Date])) RETURN CALCULATE(SUM(Data[Resting Heart Rate]), FILTER(Data, Data[Date] = endDate))
Ending Weight = VAR endDate = CALCULATE(LASTDATE(Data[Date])) RETURN CALCULATE(SUM(Data[Weight]), FILTER(Data, Data[Date] = endDate)
BMI Change = [Ending BMI] - [Starting BMI]
BMI Change Percent = [BMI Change] / [Starting BMI]
Resting Heart Rate Change = [Ending Resting Heart Rate] - [Starting Resting Heart Rate]
Resting Heart Rate Change Percent = [Resting Heart Rate Change] / [Starting Resting Heart Rate]
Weight Change = [Ending Weight] - [Starting Weight]
Weight Change Percent = [Weight Change] / [Starting Weight]
FullName = Participants[LastName] & ", " & Participants[FirstName])
Add Filters
Even though the dataset has a single participant and limited daily data, we are going to add filters in the event we have a more robust dataset in the future. We will add these as Page level filters, but they certainly could be added as slicers directly on the report, itself.
To add the filters, click the Name of the Page in the bottom area of Power BI Desktop, and choose the Filters blade. Simply drag the Date - Month and Date - Year attributes to the Filters area, and then do the same for FullName from the Participants table.
领英推荐
Visual Layout & Design
With my Adobe subscriptions, I looked through the available Stock Images that I could use as a starting point for my visual design. I ended up selecting and licensing a few infographic and icon libraries that aligned well to the data I was presenting. I used Illustrator and Photoshop to manipulate and create the PNG files for the design. In total, I created 5 unique images that I would use including: the background, icons for the Activity and Health Metrics areas, and then the 2 Metric images used at the bottom of the report.
If you do not have a license for the Adobe Creative Suite, there are some open-source tools and images libraries that can be used as an alternative. These include Inkscape for Illustrator, GIMP for Photoshop, and RawPixel to source imagery. Artists work really hard in creating these images, so make sure to properly license any images you use or find free ones listed within RawPixel. If you are new to these types of tools, there are a number of great videos on YouTube on how to do basic image manipulation.
Page Background
Visual Elements
Below is the report layout that includes the use of Text Boxes, Images, Cards, and 2 different types of charts. It is important to note, that I don't generally embed text into my images as screen sizes and resolution can cause some pixelation to occur and the final appearance is not as crisp. This can be somewhat limiting as the supported fonts in Power BI Desktop is much smaller than what I have access to in the Adobe products. If you are looking to brand your reports with corporate fonts, you will have to embed text into your images and likely work with someone experienced in optimizing those images to avoid pixelation.
Adding images and text boxes is a simple task in Power BI Desktop and accessed from the Insert menu option on the top toolbar. After those elements are added, we need to add the chart and card visuals to the canvas from the Visualizations blade. After adding the visualizations, configure the data settings using the information below and experiment with the formatting options to ensure colors and fonts match throughout the report.
Summary
I, especially, like the use of infographic-style reports for customer facing use cases. The combination of supporting content and visually appealing graphics, along with meaningful data, helps convey information in an easily consumable format. Whether you decide to display a report on a large display in a common area or use Power BI Embedded within an application, there is so much you can do with Power BI to level-up the user experience. Below are links to the assets used to produce the report in this article and a link to download and install Power BI Desktop.
Disclaimer:?All views and opinions expressed in this article are my own and do not represent those of any entity, whatsoever, with which I have been, am now, or will be affiliated.?The content is for educational purposes only and shall not be understood or construed as legal, financial, tax, medical, health, or any other professional advice.
Systems Analyst, Health IT
2 年Very cool! Congrats on your health journey!!
Leader | Investor | Teacher | Coach | Mentor
2 年Nice job James! And awesome progress personally!!
Alliances Builder - Problem Solver
2 年Anika Gardenhire, RN,CHCIO, this is what Jim was talking about the other day. Thought you might find it interesting.