BYU Student Author: @Jimmy_Han
Reviewers: @Jacob_Dutton, @James_Gerstner
Estimated Time to Solve: 20 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 manager is interested in assessing the business performance, and to facilitate this, she requires information on the total gross margin for each bike, and the number of purchases by each customer. Eager to make a positive impression as a new hire, you aim to fulfill her request using Power BI. Not having acquired the skills for elaborate visualizations yet, you plan to present your findings in a table.
Instructions
- Start by opening Power BI and bringing in all three tables from the ChallengeX_Peaks_Cycles.xlsx file. The system should automatically establish the necessary relationships between the tables as you import them.
- Next, generate measures to get a) total gross margin by each bike, b) the count of purchases by each customer.
- Add 2 tables to the canvas in Power BI to show the measures that you created in the previous step.
- Save your Power BI file as pbix.
Data Files
Suggestions and Hints
- When creating the measure to count the number of customers, use Customer ID instead of customer name to avoid using duplicates. Customer ID should be adopted from Invoice_Export table since we are counting customers who made purchases.
- Total Gross Margin = Total Sales Value – Total Cost of Goods Sold(COGS per item * Quantity Sold)
- Is the total of a column not accurate? Consider using “SumX” function instead of “Sum” function. In short, SumX is a function in Power BI used to iterate over a table, apply an expression for each row, and then sum up the results. It is important for performing row-wise calculations and aggregations, enabling more complex and flexible calculations in Power BI measures and calculated columns.
Solution
