# 162|POWERBI – Peaks Cycles, Creating Columns

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

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

2. Open Power BI Desktop: Start a new report by opening the Power BI Desktop application.
3. Get Data: Click on the “Get Data” option in the Home tab. Select “Excel Workbook”
4. Select Files: Navigate to where you’ve saved the Excel file.
5. Select Data: Check the box for all 3 sheets of data.
6. 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.

1. Open the Customer_Info table and create the “FullName and AreaCode columns.
2. Open the Bike_Export table and create the BikeProfit an ValueAdded columns.
3. 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

Here’s a screenshot of the Bike_Export table, with the ValueAdded column selected.

Challenge162Attempt.pbix (106.2 KB)
Challenge: Intermediate
Time to Complete: 15 minutes
It was fun to make some new columns in PowerBI. Learning PowerBI has been pretty enjoyable as it’s very user friendly!

Challenge162.pbix (103.0 KB)
Challenge: Intermediate
Time to Complete: 22 Minutes
The challenge utilized some of the things we went over in our PowerBI training, but expanded upon them to create some new ways of thinking.

Here is my solution! TechHubTraining.pbix (101.3 KB)

Time to complete: 30 minutes
Challenge: Intermediate
This challenge was very interesting and allowed me to use the basic understanding I already possessed with PowerBI to complete tasks, while also allowing me to learn new functions and gain a more advanced understanding of the program overall.
Challenge 162 Solution.pbix (102.5 KB)

Time to complete: 25 minutes
Rating: Beginner
Really enjoyed this!
Training Challenge.pbix (101.2 KB)

Time to complete: 20 minutes
Rating: Beginner

Enjoyed this challenge! Forced me to brush up on my DAX.
TechHubChallenge162.jh.pbix (102.4 KB)

Time to Complete : 25 minutes
Rating: Beginner
Peak Cycles.pbix (101.3 KB)

Challenge: Intermediate
Time to Complete: 26 Minutes
Most of what was required for this challenge was taught during the Intern Innovation week, but I had to play around with more of the formulas in the calculated column to arrive at the solution.

Challenge Tech Hub - Justin L.pbix (101.9 KB)

Time to complete: 20 minutes
Beginner
This was fun!
Challenge162.pbix (102.0 KB)

Time to complete: 25 minutes
Rating: Beginner
162.pbix (97.4 KB)