Showcase Power BI - Toy Store KPI Report

Maven Analytics' Guided Project - Toy Store KPI report is part of the course "Power BI Specialist".

Summary Toy Store KPI Reporting

1. What is the Business Case

Situation: As a new Data Analyst at “Maven Toy Store,” which has multiple locations across Mexico, I was tasked with analyzing the store's performance data.

Assignment: I had access to transactional sales records from January 2022 to September 2023, along with product and store location information.

Goal: To build a simple, interactive report for the leadership team to monitor key business metrics and high-level trends.

Objectives:

• Connect and profile the data.
• Create a relational model using best practice data modeling.
• Add calculated measures and fields.
• Build an interactive and visually appealing report.

Business Problem: The leadership team lacked a consolidated view of key performance indicators (KPIs) and trends across multiple store locations, making it difficult to make informed strategic decisions.

2. Show Insights & Impact

General Metrics:

• Total Orders: 0.83M (Jan 2022 – Sep 2023)
• Total Revenue: $14.44M
• Total Cost: $10.43M
• Total Profit: $4.01M

Product Category Development:

Toys: Most orders (0.22M), Revenue: $5.09M, Cost: $4.01M, Profit: $1.08M.

Downtown Location:

• Most successful in "Art & Crafts": 0.13M orders
• Highest revenue in "Toys": $0.29M
• Highest cost in "Toys": $0.23M
• Highest profit in "Art & Crafts": $0.13M

Store City Development:

Most succesful Cuidad de Mexico:

• Orders: 90,725
• Total Revenue: $1,649,492
• Total Cost: $1,183,934
• Total Profit: $465,558

Impact for the Business: These insights highlight the most successful product categories and store locations, suggesting where marketing efforts and inventory focus could be optimized. For example, the high performance of the Downtown location in 'Art & Crafts' and 'Toys' indicates potential areas for targeted promotions and inventory restocking.

3. Data Storytelling

Visualizations Used:

• KPI Cards: Overview of Orders, Revenue, Cost, and Profit
• Bookmarks: Line Chart and Map for dynamic display
• Slicers: Enhanced slicing and dicing capabilities for UI/UX
• Matrix: Breakdown by Product Category, Store City, Totals, and Store Location
• Line Chart: Displays high-level trends
• Icon Map v3 – James Dales: Geospatial analysis for sales trends across multiple store locations in Mexico

Data-Driven Storytelling:

• Each visualization supports specific business insights.
• KPI Cards: Provide an at-a-glance overview of key metrics for quick assessment.
• Line Charts: Highlight trends over time such as the identification of seasonal fluctuations and long-term growth.
• Icon Maps: Show geospatial performance, pinpointing successful locations.
• Matrix and Slicers: Allow for detailed breakdowns and interactive analysis, helping the leadership team drill down into specific areas of interest.
• Matrix contains dynamic title based upon function selected value
• Matrix contains conditional formatting (blue) for ranking highest/lowest values

10-Second Rule: Visualizations are designed to be easily understood within 10 seconds, ensuring quick and effective communication of insights.

4. Provide Technical Depth

Data Preparation:

• Reviewed table columns for blank or null values.
• Ensured correct data types and established primary and foreign keys.

Relational Model:

• Created a Star Schema with proper filter directions (1:Many) between dimensions and fact table(s).

Measures and Fields:

• Created measures (table) for total orders, revenue, cost, and profit.
• Added field parameters for extra filtering capabilities.

Geospatial Analysis:

• Used an extra dimension table “Store (Lat Long)” to merge latitude and longitude data with store dimension, ensuring accurate location registration on the icon map. The load option was disconnected post-merge to optimize performance.

Technical Challenges and Solutions:

• Challenge: Integrating geographical data for accurate visualization.
• Solution: Created an additional dimension table for store locations, enabling precise geospatial analysis.
• Challenge: Handling large datasets with blank or null values.
• Solution: Comprehensive data cleaning in Power Query to ensure data quality and integrity.

Single Page Reporting Goal:

• Presented a comprehensive yet concise single-page report that provides the leadership team with insights into key performance indicators and trends.

Conclusion:

The "Summary Toy Store KPI Reporting" project effectively consolidates and visualizes key business metrics for Maven Toy Store, providing the leadership team with valuable insights into orders, revenue, cost, and profit across various locations, store cities and product categories. The use of interactive elements and best-practice visualizations ensures that the data is presented in a clear and accessible manner.