Sales Analysis
- Aroyewun Airat
- Dec 13, 2022
- 5 min read
Updated: Sep 30, 2024
This blog post contains the process I took in creating a sales analysis report, dashboard, and app using PowerBI.
About the dataset
This dataset comes from Microsoft’s free AdventureWorksDW2014 database file. It is combined data from several tables into one fact table. Certain columns have been altered from the AdventureWorksDW2014 data.
The dataset contains the following columns:
1. Product key
2. Order Date key
3. Due Date Key
4. Customer Key
5. Sales Territory Key
6. Sales Order Number
7. Order Quantity
8. Total Product Cost
9. Sales Amount
10. Sales Territory Region
11. Sales Territory Country
12. Sales Territory Group
13. Birth Date
14. Marital Status
15. Gender
16. Yearly Income
17. English Education
18. English Occupation
19. House Owner Flag
20. Number Cars Owned
21. English Product Name
22. Color
23. English Product Subcategory
24. Ship Date
25. Flag Missed Due Date
Data Import
I imported the CSV file into power query using the "Get data" option in the Power BI desktop.
Data Profiling
I examined the data structure, Interrogated column properties, and statistics, and Identified data anomalies with the query editor using the column quality, column distribution, and column profile in the views tab.
Data Cleaning
Changed the column data type, Making sure that each column has the appropriate data type.
Removed Duplicate from the entire dataset to avoid duplicate values in the dataset.
Removed Errors from the entire dataset.
Replaced some values like Replacing ‘F’ for ‘Female’ in the Gender column.
Worked with Date and DateMath calculation in Power Query. Creating new columns like:
The Start of the Week for each Order Date. Named the column “Order Date SOW”
The End of the Week for each Shipment Date. Named the column “Shipment Date EOW”
Calculated the difference between Order Date SOW and Shipment Date EOW. Named the column “calc_Duration”.
Added calc_Duration from the previous step to the Shipment Date, then subtracted 4 days, all in one column. and Named the column “Random Calculation”.
Added a column that evaluates whether the Order Date SOW column is a date. The column returns 1 if true and 0 if false. Named the column “Date test.”
Added a column called Age. Values in the column contained the customer’s current age, based on the difference between the BirthDate column and today’s date.
Database Normalization
Database Normalization is the process of organizing the tables and columns in a relational database to reduce redundancy and preserve data integrity.
I will go ahead to separate the dataset into lookups and Fact tables using Primary and Foreign keys to create relationships between the table.
Product Dimension Table
1. Product Key
2. English Product Name
3. Color
4. Total Product Cost
5. Sales Amount
Product Subcategory Dimension Table
6. Product Subcategory Key
7. English Product Subcategory Name
Combined Fact Table
8. Sales Order Number
9. Order Date Key
10. Order Day
11. Order Quantity
12. Product Key
13. Product Subcategory Key
14. Sales Territory Key
15. Customer Key
16. Due Date Key
17. Flag Missed Due Date
18. Ship Date
19. Order Date SOW
20. Shipment Date EOW
21. Calc Duration
22. Date Test
Territory Dimension Table
23. Sales Territory Key
24. Sales Territory Region
25. Sales Territory Country
26. Sales Territory Group
Customer Dimension Table
27. Customer Key
28. Birth Date
29. Age
30. Marital Status
31. Gender
32. Yearly Income
33. English Education
34. English occupation
35. House owner Flag
36. Number Cars Owned
Data Modeling
Data Modeling is the process of creating relationships between multiple datasets. These Relationships are established by relating the Primary Key and Foreign Key.
I created a one-to-many{1:*} relationship between the Fact and Dimension table.
I created a snowflakes schema Product subcategory, Product dimension and CombinedFactTable.
DAX
Calculated Table:
Created a Calendar table using the CALENDAR AUTO function.
Created a Customer Dimension table using the SUMMARIZE function.
Calculated Column
Created a calculated column called “Due Date” that transforms the DueDateKey value in each row to a date value.
Created a calculated column called “OrderDate” that transforms the OrderDateKey value in each row to a date value.
Created a calculated column called “DaysUntilShip”. The value in each row contains the difference between the ShipDate in that column, and the OrderDate you created in the previous step.
Created a calculated column called “Order Date EOM”. The column displays the end of the month (last date of the month) for each Order Date.
Created a calculated column called “Shipment Date FOM”. The column displays the start of the month (first date of the month) for each Shipment Date.
Calculated Measure
Created a Measure called "Orders Past Due date". This measure will use the FlagMissedDate column to determine which orders were past due and which were not.
Created a Measure called "Net Revenue" subtracting the sales amount from the Total product cost.
Created a Measure called "Avg Days to Ship"
Created a Measure called "Total Orders" which is the number of orders.
Groups
Added a group that gathers each value in the Age column into “bins”. The size of each bin should be 10.
Visualization
For the visualization,
I used the Colorblind Safe color theme
I used the 4:3 canvas type.
I added 4 report pages which include:
a. Executive summary: This report page contains the key visuals about the sales report. This page contains the following visual:
i. 4 KPI card visuals that compare previous year metrics vs current year metrics. For example, Current year Order vs previous year Order.
ii. 2 card visuals showing the Top Territory Region by Net Revenue and Top Product by net revenue.
iii. A bar chart showing Sales countries by Net Revenue.
iv. A Metric visual displaying Territory Group and Territory country in the rows and Net Revenue, Avg Days to ship, and Orders Past Due in the column. Using a drill-down option to drill between Territory Country and Territory Group.
b. Product Analysis: This report page contains an analysis focusing on products only. This page contains the following visual:
i. 4 card visuals displaying the Total number of products, total number of product subcategories, The top product by net revenue, and The bottom product by net revenue.
ii. A metric visual displaying the Product Name, Net Revenue, Total Orders, and Avg Days to ship.
iii. A bar chart displaying Product subcategories by Net Revenue filtered in the quarter. A bookmark is used to select between quarters.
c. Customer demography: This report page contains an analysis of customer demography. This page contains the following visual:
i. Year Slicer, Gender Slicer, Territory Group slicer, and Education level slicer.
ii. A column visual displaying the Customer’s occupation by Net Revenue.
iii. A tree map visual displaying Female orders by All countries.
iv. A column chart displaying Customer’s age bins by Net revenue
v. A doughnut chart displaying Customer gender by Net revenue.
d. Insight: This report page contains AI visuals like Q and A visuals, Key Influencers, and a Decomposition tree. Used bookmarks to move through each visual by selecting the visual name.
Edit Interaction: I edited the interaction between the visuals.
Dynamic Title: I used SELECTVALUE, COMBINEDVALUES, IF and HASONFILTER to create a dynamic title when a filter is applied.
Power BI Service
I created a Workspace and named it “Adventure Works”. I granted two other teammates access and permission.
I published the report from Power BI Desktop to Adventure Works workspace in Power BI service.
I pinned the entire report to a Dashboard.
After a complete review of the dashboard, I endorsed the dashboard and proceed to create an App to share the dashboard with other users within the organization.
Link to Adventure Works App:
Comments