Play Smarter - A Toy Store Dashboard Where Data Can Be Fun!

SITUATION: Maven Toys, a fictional toy store chain in Mexico, has multiple locations. As a newly hired Data Analyst, my assignment was to access transactional records from January 2022 to September 2023, along with data on locations, products, and inventory. The goal was to create an interactive report in Power BI to help the leadership team monitor key business metrics and trends.

I decided to create a one-page exploratory dashboard focusing on key metrics related to revenue, profit, and units sold.

Step 1: Connect and Profile the Data I connected to the calendar, inventory, product, sales, and stores files. In Power Query, I checked for blank or null values, ensured all data types were correctly defined, and identified primary and foreign keys. Profiling the data, such as transaction counts, store numbers, and product price ranges, helped determine the key metrics to focus on. I also added calculated columns to the calendar table, such as Start of Month, Start of Week, and Day Name, to enhance measure usability.

Step 2: Create a Relational Model The tables were loaded into the data model, and relationships were established between the data and lookup tables using a star schema. Lookup tables were positioned above data tables, indicating data flow, and connected via primary and foreign keys.

Step 3: Add Calculated Measures and Fields This step required significant time investment. I identified key metrics important for the leadership team, focusing on revenue, profit, and units sold/inventory. Measures were created for cost, orders, profit, revenue, and units sold, covering previous months, the current month, and totals.

Step 4: Build an Interactive Report I spent the remaining time on this step, iterating the dashboard five times to reach the final version shown in my portfolio. The top of the report includes a color legend, a slicer for store location filtering, and a simple company identifier.

Key Metrics:

All three key metrics—Revenue, Profit, and Units Sold—were displayed using cards for emphasis. A KPI card shows Current Month vs. Previous Month, with color and icon indicators for changes. Additional visualizations for specific metrics are as follows:

1. Revenue:
   • Line chart for revenue, cost, and profit trends, with interactive filtering and drill-down/up features.
2. Profit:
   • Column chart shows profit by product category.
   • Tooltip reveals the top 5 products by profit margin when hovering over a category.
   • Highlights top and bottom products.
3. Units Sold:
   • Bar chart displays the highest volume days of the week by total units sold.
   • Tooltip reveals top 5 items sold when hovering over any day of the week.
   • Bar chart compares Average Units Sold per Month to Units in Inventory, highlighting supply chain discrepancies needing correction to avoid losing sales of high volume/high profit items.

Using Power BI's robust capabilities, I was able to transform raw transactional, product, and inventory data into a comprehensive, interactive dashboard for Maven Toys. Power BI's data connection and transformation tools allowed for efficient data cleaning and profiling, ensuring accurate data types and key identification. The platform's modeling features facilitated the creation of a star schema, seamlessly linking data and lookup tables. Advanced DAX functions enabled the development of calculated measures, providing insights into key metrics such as revenue, profit, and units sold. Power BI's dynamic visualization options, including KPI cards, line charts, column charts, and bar charts, allowed for an engaging and interactive report, empowering the leadership team to monitor business performance and trends effectively.