BYU Student Author: @James_Gerstner
Reviewers: @Jimmy_Han, @Jacob_Dutton
Estimated Time to Solve: 30 Minutes
This is an intro challenge that is part of the Power BI Learning Path.
We provide the solution to this challenge using:
- Power BI
Need a program? Click here.
Overview
You’ve just landed the role of Financial Analyst at Peak Cycles, a bustling bike shop known for its quality and variety. Your first task is a vital one: dive into our shop’s data, clean it up, and get it ready in Power BI for analysis.
You’ve already done some data manipulation (in previous Peaks Cycles challenges), but now it’s time to show off your data visualization skills by creating a couple of compelling visuals in Power BI. You heard your manager saying he likes Waterfall charts and Heat maps, so you’ve decided to make one of each of those, as well as a couple of basic visualizations:
- A Card can highlight a single value, which will change as you select sections of different visuals. You want to make a card that will display total profit.
- A Stacked column chart can serve as a basic bar chart to show data values for categories like products, regions, or months. You want to use one to show the profit by quarter.
- The Heat map will show where your customers are located, as designated by their zip codes. You want to be able to see the distribution easily, and zoom in and out using buttons.
- The Waterfall chart will describe how profits per order size change throughout the year. You’ve noticed that the number of bikes per order changes seasonally, and you want to see how those size changes affect quarterly profitability. The Waterfall chart will show the quarterly changes to help you investigate.
Instructions
Open the completed Peaks Cycles - Creating Columns .pbix file where you have your data. If you do not already have the prepared data from the Peaks Cycles - Creating Columns challenge saved, download the provided .pbix file (and check out the other Learning Path challenges!). Make sure the Visualizations and Data panes are expanded from the right sidebar, then it’s time to generate some insights!
Note: this challenge breaks everything down into tiny pieces, so don’t be intimidated by the number of steps—it seems like a lot, but it’ll go by fast.
First, make a card. Select the “Card” icon from the Visualizations pane.
- Expand the Invoice_Export table and select the InvoiceProfit data or drag it into the “Fields” area. Notice that the card auto-populates with the sum of the column.
- Format the card by selecting the “Format your visual” tab in the Visualizations pane.
- Toggle the “Category Label” button to “off” and navigate to the “General” tab.
- Toggle the “Title” button to “on,” then change the title to “Total Profit” and change the font size to 20 so it’s more noticeable.
Next, make a bar chart. Select the “Stacked column chart” icon from the Visualizations pane.
- Select the InvoiceProfit and SaleDate columns in the Data pane.
- In the X-axis field, remove the “Year” and “Day” fields, so you’re left with only “Month” and “Quarter.”
- Navigate to the Title section again, and name it “Total Profit by Quarter and Month.”
Next, build the heat map using the AreaCode to determine where your customer base is.
- Select the “Map” icon from the Visualizations pane.
- In the Data pane, expand the Customer_Info table. Drag the AreaCode data into the Location field (where it says “Add data fields here”) in the Visualizations pane.
- If your map isn’t working, be sure you have the maps setting enabled. Go to File – Options and Settings [at the bottom] – Options – Security – Maps and Filled Map Visuals [at the bottom]. Toggle that setting so you can use Map visuals. Now, the map should appear on your dashboard.
- Near the top of the Visualizations window, click on the “Format Visual” tab. Toggle the Heat map button at the bottom of the list. Expand the Heat Map section and change the color scheme so it stands out better. Then, collapse the Heat Map section.
- Expand the Map settings section at the top of the list. Expand the Controls subsection and toggle the zoom buttons feature.
- Finally, navigate to the “General” subsection in the Visualizations pane. Make sure Title is toggled on, and expand that section. Rename the visual to “Customer Location.”
- Admire your Heat map! Which regions should you focus on for advertising and customer outreach?
Click off the Heat map to get ready to build the Waterfall chart.
- Select the “Waterfall chart” icon from the Visualizations pane.
- Expand the Invoice_Export table and drag the SaleDate data from the Data pane into the Category field. Deselect the “Year” and “Day” filters. This will split up your X-axis by quarter so you can analyze the changes over time.
- Drag the Quantity data from the Data pane into the Breakdown field. This will further split up the X-axis to describe your data by the number of bikes per invoice (1, 2, 3, or 4).
- Drag the InvoiceProfit data into the Y-axis field. This will finish your visual and provide data about how much profit you made in each quarter, from each order size.
- Navigate to the “Format Visual” section and toggle the Legend off. Expand the Y-axis section and toggle the Title off.
- Expand the Columns section and change the color scheme to something more visually appealing.
- Toggle the Data labels section to “On” and expand the Data labels section and Options subsection. Change Orientation to “Horizontal” and Position to “Outside end.”
- Finally, navigate to the “General” subsection in the Visualizations pane. Make sure Title is toggled on, and expand that section. Rename the visual to “Change in Profits by Order Size.”
- Explore your Waterfall chart to see which order sizes are driving profits in each of the four quarters! Note that the labels and bars represent the change in profits for each order size as you go from quarter to quarter. When do you get the greatest number of large orders? What can you do to take advantage of or smooth the inconsistent sales?
Take a second to click through your profit visualizations to see how they interact with one another—this can help you understand why Power BI is such a valuable skill to employers or others to whom you will present data.
Data Files
Solution
