Our Assignment
Cayden Santis - 134
Jeshurun Sabu - 159
Suraj Sanki - 132
The dataset consists of multiple sheets, each with different types of data related to sales.
Here’s a breakdown of what each sheet might contain:
1. Sales Order_data: Likely contains detailed information about individual sales
orders.
2. Sales Territory_data: Probably covers data about different sales regions or
territories.
3. Sales_data: Likely includes overall sales metrics or transactions.
4. Reseller_data: Likely holds information about resellers involved in the sales
process.
5. Product_data: Likely contains information about the products being sold.
6. Customer_data: Likely includes customer information.
Overview -
Sales Order_data: Contains fields like 'Channel', 'SalesOrderLineKey', 'Sales Order',
and 'Sales Order Line'.
Sales Territory_data: Includes fields like 'SalesTerritoryKey', 'Region', 'Country',
and 'Group'.
Sales_data: Appears to have some unnamed columns, so the structure isn't clear.
This may need some cleanup.
Reseller_data: Contains information about resellers, such as 'ResellerKey',
'Reseller ID', 'Business Type', 'Reseller', 'City', 'State-Province', 'Country-Region', and
'Postal Code'.
Product_data: Includes product-related data like 'ProductKey', 'SKU', 'Product',
'Standard Cost', 'Color', 'List Price', 'Model', 'Subcategory', and 'Category'.
Customer_data: Contains customer information such as 'CustomerKey', 'Customer
ID', 'Customer', 'City', 'State-Province', 'Country-Region', and 'Postal Code'.
Practical Exercise Outline for Power BI:
Step 1: Load Data into Power BI
Load the provided Excel sheets into Power BI, keeping the relationships between them
in mind. Sheets to import:
Sales Order_data
Sales Territory_data
Sales_data
Reseller_data
Product_data
Customer_data
Step 2: Data Relationships
Explain the relationships between tables (e.g., how Sales Orders relate to Products,
Customers, and Resellers).
Use Manage Relationships in Power BI to establish links between the tables:
Connect Sales Order_data with Customer_data via the customer key.
Link Sales Order_data with Product_data via the product key.
Establish a connection between Sales Territory_data and Reseller_data using the region
or country.
Step 3: Clean Data
Handle missing or unnamed columns in the Sales_data sheet. Students should rename
the unnamed columns as needed based on analysis or guidance.
Use the Transform Data feature to clean and format columns, ensuring that all data
types are correct.
Create Date Table -
Date =
ADDCOLUMNS(
CALENDAR(DATE(2015, 1, 1), DATE(2025, 12, 31)),
"Year", YEAR([Date]),
"Month Number", MONTH([Date]),
"Month Name", FORMAT([Date], "MMMM"),
"Day", DAY([Date]),
"Weekday Name", FORMAT([Date], "dddd"),
"Quarter", QUARTER([Date]),
"Year-Month", FORMAT([Date], "YYYY-MM")
)
Step 4: Data Visualization Tasks
Sales Performance Dashboard: Create a dashboard that shows:
Total sales by product categories.
Sales performance across different regions and resellers.
A trend of sales over time (assuming sales dates are available).
Customer Analysis: Create a report that:
Shows top customers by revenue.
Groups customers based on geographic location (City, State-Province, Country-Region).
Analyzes customer buying patterns.
Product Performance:
Identify best-selling products by total sales.
Compare the profitability of different product categories using 'Standard Cost' and 'List Price'.
Reseller Insights:
Map the distribution of resellers by region and type of business.
Analyze reseller contributions to total sales.
Step 5: Advanced Analysis (Optional)
Use DAX functions to create custom measures:
Calculate sales growth by region or product category.
Compute the average sale per customer.
Derive profitability metrics (e.g., gross profit by product).
1. Sales Growth by Region
DAX Code:
Sales Growth =
VAR CurrentPeriodSales = SUM(Sales_data[SalesAmount])
VAR PreviousPeriodSales = CALCULATE(
SUM(Sales_data[SalesAmount]),
SAMEPERIODLASTYEAR(Sales_data[SalesDate])
)
RETURN
IF(
ISBLANK(PreviousPeriodSales),
BLANK(),
(CurrentPeriodSales - PreviousPeriodSales) / PreviousPeriodSales
)
2. Average Sales per Customer
DAX Code:
dax
Copy code
Average Sales per Customer =
DIVIDE(
SUM(Sales_data[SalesAmount]),
COUNTROWS(Customer_data)
)
3. Gross Profit by Product
DAX Code:
dax
Copy code
Gross Profit =
SUMX(
Sales_data,
(Product_data[List Price] - Product_data[Standard Cost]) * Sales_data[Quantity]
)
4. Cumulative Sales (Running Total)
DAX Code:
dax
Copy code
Cumulative Sales =
CALCULATE(
SUM(Sales_data[SalesAmount]),
FILTER(
ALL(Sales_data[SalesDate]),
Sales_data[SalesDate] <= MAX(Sales_data[SalesDate])
)
)
5. Sales by Product Category
DAX Code:
dax
Copy code
Sales by Category =
CALCULATE(
SUM(Sales_data[SalesAmount]),
Product_data[Category] = "CategoryName"
)
6. Profit Margin
DAX Code:
dax
Copy code
Profit Margin =
DIVIDE(
[Gross Profit],
SUM(Sales_data[SalesAmount])
)
7. Top Reseller by Sales
DAX Code:
dax
Copy code
Top Reseller =
CALCULATE(
MAXX(
VALUES(Reseller_data[Reseller]),
SUM(Sales_data[SalesAmount])
)
)
8. Sales Forecasting (Simple Moving Average)
To forecast future sales using a simple moving average, you can calculate the average sales
over a defined period.
DAX Code:
dax
Copy code
Sales Forecast =
AVERAGEX(
DATESINPERIOD(
Sales_data[SalesDate],
LASTDATE(Sales_data[SalesDate]),
-6,
MONTH
),
[Total Sales]
)
