Explore My Project

Welcome to my Power BI project, where I designed and developed a dashboard to analyze key sales metrics for a fictitious apparel company using three interconnected data tables as a resource.

Introduction

In my senior year of college, I took a python class where one of the units involved finding datasets online and cleaning them using the Pandas library. This sparked my interest in data analytics and I decided to learn more about it as a career path. Through networking opportunities with industry professionals and online research, I gained a deeper understanding of the importance of data analytics and its critical role in helping companies make informed decisions. In one of these networking occasion, I learned about a data visualization software called Microsoft Power BI that is a prevalent tool in the data analyst field. I then began this project in order to develop some familiarity with Power BI and build experience that will support my growth as a data analyst.

To begin, I was supplied with a mock company dataset by one of my professional contacts, who also granted me permission to use and publish it. I made it my goal to derive insights from the data that could be useful for increasing the company’s revenue. The first step was to familiarize myself with the information provided in the data and the relationships between the data tables. This was followed by importing the data base into Power BI and ensuring that the tables had the necessary fields to create informative visualizations. Part of this data preparation stage included adding specific time-related fields in order to track the company's annual and monthly sales. Next, was creating visuals that measured the data across various KPI's such as sales, margin, margin percentage, order count, average order value, and quantity of products sold. The purpose of this was to break down every facet of the company to clearly identify its weaknesses and strengths. Finally, by leveraging these visuals, I was able to uncover meaningful insights that could guide strategic decisions, helping the company address its weaknesses, capitalize on its strengths, and ultimately achieve greater profitability.

The Data

The company in this project sells apparel to businesses around the world. All of the data for these sales is contained within three interconnected tables. The Orders data table contains all of the order information which allows us to track sales across various metrics.

The Employee table links to the Orders table through the 'EmpID' column which is later facilitated using the 'Manage relationships' tool within Power BI. This table allows for studying of each individual sale by employee.

The Customers table links to the Orders table through the 'CustomerID' column and allows for analysis of sales data on a per customer basis.

It should be noted that these tables contain more rows than what is shown here as I am limited to posting screenshots. The orders table consists of data for a total of 2172 transactions that accurately mimic the sales data of a small apparel company. The link to the full data set can be accessed through this link:

https://www.kaggle.com/datasets/jojopopo159/mock-apparel-company-data

Dashboard

An overview of the interactive dashboard created for this project. This page highlights key visuals designed to provide insights along with their descriptions.

Page 1: Gross Sales

The Seasonal Sales bar chart graphs the gross sales of each month throughout the history of the company. The chart reveals that March, April, and December are the highest grossing months while the lowest is February.

The Country Sales pie chart shows the distribution of the company's sales across different countries. Referencing the chart, the company has made 30.6% and the most of its sales with customers in Germany, with the USA being the second highest at 10.48%.

The Annual Sales Comparison line chart breaks down the company's yearly sales. Using the chart, it can be observed that the first half of 2008 saw massive leaps in sales from the years prior, only to drastically fall and meet the sales of November, 2005.

Page 2: Margin

The Margin Details bar and line chart shows KPI's for margin and margin percentage. Included is a margin target to help visually identify whether the margin for each client meets a 20% goal. The company has profited the most with Grunewald while also maintaining a margin percentage above 20%.

The KPI - Margin Goal: 20% table lists each sales representative that works for the company and compiles each sale they have made. Ingrid Hendrix is responsible for some of the lowest sales, and at the lowest margin while Rob Carsson has sold the most product at 1.2% above the margin goal.

The Order Details Matrix lists each individual sale the company has made with each customer. To increase interactivity, clicking on a customer activates a drop-down menu that allows the user to select any Order ID associated with the customer, providing the order date and product information (Grunewald example shown below).

Page 3: Key Metrics

The Order Details bar and scatter chart graph the average order value of each customer in addition to the order count. Referencing the chart, Sunny Ski Store has placed the highest value orders with the company but has only placed a small amount, leading to lower gross sales compared to other clients.

The Top Country Sales tree-map is identical to the pie chart on the first page, except with greater usability. Upon clicking on a country, the tree-map will convert to show each customer within the region in the order of gross sales (Example for USA shown below)

The Margin Percent gauge is an interactive visual that dynamically adjusts to reflect the margin percentage for filtered selections within the dashboard. This interactivity enables users to quickly assess whether the margin goal is being met for any selection on the page.

The KPI Row is a row of five metrics that show Sales, Orders, Average Order Value, Minimum Sale, and Maximum Sale based on selections. Like the margin gauge, it enables users to quickly identify patterns and assess performance for any selection on the page.

Insights

This section highlights key findings derived from the dashboard analysis. Each insight provides observations and recommendations based on the visualizations, offering a deeper understanding of trends, performance metrics, and opportunities for growth.

On Page 3, after selecting Boleros from the Order Details visual, the KPI Row and Margin Gauge adjust to reflect the values associated with all Boleros transactions. They stand out as a highly valuable customer for this company. With gross sales of $129K across 48 orders, they demonstrate a consistent purchasing pattern. Their high average order value of $2.7K, combined with a maximum sale of $14.5K, suggests they are likely a key client for large-scale transactions. Additionally, their 22.9% margin exceeds the target margin of 20%, indicating they are not only contributing significantly to revenue but are also meeting profitability goals. This makes Boleros a prime candidate for targeted retention strategies or potential upselling opportunities.

On Page 2, we can select specific customers in the Margin Details visual to analyze their associated sales representatives and the margins achieved for those sales. This feature is useful for identifying trends and addressing sales that fall below the margin target of 20%, which can significantly impact the company’s profitability.

Customers La Moda Pasada and La Sais de Rió have multiple sales below the target margin, all handled by the same representatives, Tom Lindwall and Leif Shine. Specifically, Leif Shine made sales of $1,892.24 and $6,444.50 at margins of 8.1% and 15.2%, respectively, falling well short of the target. Similarly, Tom Lindwall recorded sales of $2,341.56 and $7,837.13 with margins of 13.6% and 12.0%. These figures indicate a consistent pattern of underperformance in meeting margin goals.

To address this issue, it may be beneficial to review these transactions with the sales representatives to understand the circumstances behind these low-margin deals. Additionally, providing targeted training or establishing stricter pricing guidelines could help ensure future sales align with the company’s profitability objectives.

The Pie Chart on Page 1 shows that Germany is the leading country in terms of sales by a large margin. I wanted to see if the company could develop a strategy to boost sales in lower-performing countries so I decided to create a visual that compares the top 10 selling products in Germany and Austria (Pie chart reveals disparity between sales in Germany vs. Austria). This visual revealed that both markets share similar customer preferences, as evidenced by the overwhelming success of the Halter Dress in both regions. In Austria, this product constitutes 67.4% of sales among the top 10, compared to 50.36% in Germany, highlighting a strong alignment in demand for certain products.

Additionally, the performance of products like the Le Baby Dress, which accounts for 16% of Austrian top 10 product sales compared to only 4% in Germany, suggests that Austrian customers may respond even more favorably to high-performing products from Germany. Interestingly, among Germany’s top 10 sellers include three products—Davenport Shoes, Feiss Fleece Trousers, and Terence Top—that have not yet been sold in Austria. Given the observed similarities between these markets, introducing these products to the Austrian market represents a significant opportunity to diversify product offerings and boost overall revenue. Testing these products in Austria could help replicate their success in Germany and drive further growth in the Austrian market.

Conclusion

This project has been a valuable introduction to the field of data analytics and the capabilities of Microsoft Power BI. By working through data preparation, visualization, and then generating insights, I was able to simulate how analytics can inform decision making and help drive revenue growth for a company. From identifying valuable clients to analyzing product performance across markets, this project has allowed me to explore meaningful ways to interpret and act on data.

This is just the beginning of my journey in data analytics, and this project has given me a solid foundation in tools and techniques that I plan to build upon in future projects. My goal is to continue developing my skills in data cleaning, modeling, and providing strategic observations, and take my abilities to the workplace. Ultimately, I aim to contribute to real-world business success through finding data-driven solutions.