BYU Student Author: @James_Gerstner
Reviewers: @Jacob_Dutton, @Jimmy_Han
Estimated Time to Solve: 25 Minutes
This is an intro challenge that is part of the PowerBI 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.
For this challenge, your manager has told you that they want you to gather other data hidden inside the freshly cleaned data you have provided. You decided to use DAX (Data Analysis Expressions) to create calculated columns to add on to the current tables. There are five columns you want to create:
In the Customer_Info table:
- A column (FullName) that gives the customers’ full names, formatted as First Last, so they can be easily addressed.
- A column (AreaCode) that shows the area code from the customers’ phone numbers, so you can potentially gather data about the location of your clientele.
In the Bike_Export table:
- A column (BikeProfit) that shows the profitability of each bike model, so management can make decisions regarding product lines. Format this as “Currency” with two decimal places.
- A column (ValueAdded) that gives a descriptive analysis of the profitability column, in order to make it easier to interpret and gain insights from the data. This column should return one of three values (“Loss,” “Below Average,” or “Above Average”) based on the profitability of the bike (below zero, below the average BikeProfit, above the average BikeProfit, respectively).
In the Invoice_Export table:
- A column (InvoiceProfit) that shows the total profit of each invoice (this should reference the Bike_Export table) so your gross profit percentage for each sale can be easily found. Format this as “Currency” with two decimal places.
Instructions
If you do not already have the cleaned Peaks Cycles data downloaded:
- Download the data.
- Open Power BI Desktop: Start a new report by opening the Power BI Desktop application.
- Get Data: Click on the “Get Data” option in the Home tab. Select “Excel Workbook”
- Select Files: Navigate to where you’ve saved the Excel file.
- Select Data: Check the box for all 3 sheets of data.
- Load Data: Select “Load Data” and navigate to the “Table View” on the left sidebar.
Otherwise, open the Power BI file where you have your previously cleaned data.
- Open the Customer_Info table and create the “FullName and AreaCode columns.
- Open the Bike_Export table and create the BikeProfit an ValueAdded columns.
- Open the Invoice_Export table and create the InvoiceProfit column.
InvoiceProfit Hint
To build this column, use the DAX formula “RELATED.” This functions similarly to an XLOOKUP in Excel, where it finds related data in another table. For example, while you’re in the Invoice_Export table, in order to reference the COGS column in the Bike_Export table you would write: RELATED(Bike_Export[COGS]).
Data Files
Suggestions and Hints
- For the FullName column, you can use either the “CONCATENATE” function or “&” to concatenate the first and last names.
- For the AreaCode column, use the “LEFT” function to get the first three digits of the phone numbers.
- For the ValueAdded column, create a nested “IF” expression. This can be done by writing two “IF” statements, then pasting one inside the other.
- Use the “AVERAGE” function in the last IF statement to separate the data into the above or below average categories.
- There are a few ways to calculate the InvoiceProfit column. One way is to multiply the bike model’s COGS value by the number of bikes sold on that invoice, then subtract that from the TotalSaleValue column.
Solution
Challenge162_Solution.pbix
Solution Video: Challenge X|POWERBI – Peaks Cycles, Creating Columns
Here’s a screenshot of the Bike_Export table, with the ValueAdded column selected.
