Toy Store KPI Report

Project Goals

The goal of the project is to build a simple, interactive report that the leadership team can use to monitor key business metrics and high-level trends.

Data Set

The data set contains transactional records from January 2022 - September 2023, along with information about products and store locations.

Project Objectives

1. Connect and profile the data
2. Create a relational model
3. Add calculated measures & fields
4. Build an interactive report

Project Execution and Results

1. Connect and profile the data

   • The Sales table looked good, no empty or error values were found. Column formatting was correct. Sales_ID is the primary key and Date, Store_ID, Product_ID are foreign keys. The table was renamed to MavenToys_Sales.
   • In the Products table the Product_Cost and Product_Price were formatted as text with the $ sign and a period (.), rather than fixed decimal with a comma (,) (i.e. "$9.99" instead of "9,99"). This was resolved by removing the $ sign from the string, replacing the period with a comma, and the converting the column to fixed decimal. We're using European/Nordic settings in Power BI, which is a probable reason for the periods not being accepted. The Primary Key is Product_ID, and there are no foreign keys in this table. The table was renamed to MavenToys_Products.
   • The Stores table looked good, no empty or error values were found. Column formatting was correct. Store_ID is the primary key, and there are no foreign keys. All the dates in Store_Open_Date are outside the scope of the Calendar table, and therefor not a foreign key. The table was renamed to MavenToys_Stores.
   • The Calendar table looked good, no empty or error values were found. Column formatting was correct. Date is the only column in the table and the primary key. Columns for Start of Month and Start of Week were added. The table was renamed to MavenToys_Calendar
   • Some insights from the data was that Maven Toys operates 50 stores in 29 cities. There are 35 products in 5 categories in their assortment. The most expensive product is "Lego Bricks" at $39,99 sales price, and the cheapest is "PlayDoh Can" at $2,99 sales price.

2. Create a relational model

3. Add calculated measures & fields

• Sales table
  • Added field Cost = RELATED(MavenToys_Products[Product_Cost])
  • Added field Price = RELATED(MavenToys_Products[Product_Price])
  • Added field Revenue = MavenToys_Sales[Price] * MavenToys_Sales[Units]
  • Added field Profit = (MavenToys_Sales[Price] - MavenToys_Sales[Cost]) * MavenToys_Sales[Units]
  • Added measure Total Orders = COUNT(MavenToys_Sales[Sale_ID])
  • Added measure Total Revenue = SUMX(MavenToys_Sales, MavenToys_Sales[Units] * RELATED(MavenToys_Products[Product_Price]))
  • Added measure Total Profit = SUMX(MavenToys_Sales, MavenToys_Sales[Units] * (RELATED(MavenToys_Products[Product_Price])-RELATED(MavenToys_Products[Product_Cost])))

4. Build an interactive report

• Added KPI cards for Total Orders, Total Revenue and Total Profit, with trendlines by month.
• Added bar chart for Total Orders by Product Category.
• Added line chart for Total Revenue over time (Month/Week/Date)

Possible Future Improvements

• Adding Previous Months Orders/Revenue/Profit to add an extra dimension to the KPI cards and trend insights.
• Drill down from "Total Orders by Product Category" to a Product Category page showing individual product sales data with in said Product Category.