Hi, I’m Fahad Farooq
a Data Analyst Data Scientist BI Analyst

The Situation: 

Adventure Works is a fictional global manufacturing company that produces cycling equipment and accessories, with activities stretching across three continents (North America, Europe, and Oceania). Our goal is to transform their raw data into meaningful insights and recommendations for management. More specifically, we need to:

• Track KPIs (sales, revenue, profit, returns)
• Compare regional performance
• Analyse product-level trends
• Identify high-value customers

The Data: 

We’ve been given a collection of raw data (CSV files), which contain information about transactions, returns, products, customers, and sales territories in a total of eight tables, spanning from the years 2020 to 2022.

The Task: We are tasked with using solely Microsoft Power BI to:

• Connect and transform/shape the data in Power BI’s back-end using Power Query
• Build a relational data model, linking the 8 fact and dimension tables
• Create calculated columns and measures with DAX
• Design a multi-page interactive dashboard to visualize the data in Power BI’s front-end

The Process:

1. Connecting and Shaping the Data

Firstly, we imported the data into the Power Query editor to transform and clean it. The next process involved:

Removing Duplicates: Duplicate entries were removed from the dataset to ensure accurate analysis.

Handling Null or Missing Values: For some columns, missing values were replaced with defaults or averages. Null values in “key” columns were removed using filters.

Data Type Conversion: Columns were converted to appropriate data types to ensure consistency. Dates were converted to Date type, numerical columns to Decimal or Whole Numbers, and text columns to Text.

Column Splitting and Merging: Several columns were split to separate concatenated information, or merged to create a unified name (such as Customer Full Name).

Standardising Date Formats: All date columns were formatted consistently to facilitate time-based analysis. This step was important for ensuring accurate time-series analysis in Power BI.

Removing Unnecessary Columns: Irrelevant columns were removed to streamline the dataset. This helped focus the analysis on relevant information, reducing memory usage and improving performance.

2. Building a Relational Data Model

Secondly, we modeled the data to create a snowflake schema. This process involved creating relationships between the dimension and fact tables, ensuring cardinalities were one-to-many relationships.

Enabling active or inactive relationships, creating hierarchies for fields such as Geography (Continent-Country-Region) and Date (Start of Year-Start of Month-Start of Week-Date), and finally hiding the foreign keys from report view to ease the data analysis and visualization steps and reduce errors.

3. Creating Calculated Columns and Measures

Next, we used Power BI’s front-end formula language, DAX, to analyze our relational data model and create several calculated columns (for filtering) and measures (for aggregation), that we could later reference and use when analyzing and visualizing the data.

We used calculated columns to determine whether a customer is a parent (Yes/No), a customer’s income level (Very High/High/Average/Low), a customer’s priority status (Priority/ Standard), and the customer’s educational level (High School/ Undergrad/ Graduate).

The list of calculated measures is available below and includes key information on revenue, profit, orders, returns, and more.

4. Visualising the Data

The final step of the project was creating a multi-page interactive dashboard, including a range of visuals and KPIs that could serve management and lead to informed decision-making. We used several visuals and tools to demonstrate and visualize the data across the 4 report pages, including KPI cards, line and bar charts, matrices, gauge charts, maps, donut charts, and slicers. We made sure the report was fully interactive and simple to navigate, with icons used to enable filters, cancel filters, and guide users to each report page with ease. Features such as drill-through, bookmarks, parameters, and tooltips were also used throughout the dashboard, further enhancing its usefulness and impact on management.

Executive Dashboard: The first report page provides a high-level view of Adventure Works’ overall performance. We used card visuals to present Key Performance Indicators such as overall revenue, profit margins, total orders, and return rates. We also included additional cards to compare current and previous month performances, providing insights into recent trends, a line chart to visualize the trending revenue from 2020-2022 and highlight long-term performance, and presented the number of orders by product category to aid in understanding product sales distribution, and used a further table to display the top 10 products based on key indicators (total orders, revenue, and return rate).

Map: The second report page consisted of a map visual, an interactive representation of sales volume across different geographical locations. This offered insight into Adventure Works’ global sales distribution and worldwide reach.

Product Detail: The third report page focuses on detailed product-level analysis. It displayed detailed product information for the selected top 10 products from the Executive Dashboard, using the drill-through feature. It also included gauge charts presenting actual performance vs target performance of monthly orders, revenue, and profit, and included an interactive line chart to visualize potential profit adjustments when manipulating the price of the product, aiding in strategic decision-making regarding pricing strategies. This report page also included a line chart including key weekly product information on total orders, revenue, profit, returns, and return rate.

Customer Detail: The fourth and final report page provided a deeper insight into customer behavior and value. It used donut charts to break down customer groups into income level and occupation categories vs. total orders, helping in customer segmentation tactics, and used a matrix aided by KPI cards to identify high-value customers based on order and revenue contributions, aiding in identifying high-value customers and sales opportunities.

The Situation: 

Market Mindz is a firm that collaborates with a retail vendor specializing in food and beverage products. Our task is to analyze and evaluate the marketing company and its customers. We have to give insights around a few specific items of interest:

• How our products are performing?
• Who are our customers?
• How our 6 recent marketing campaigns performing?
• What is driving campaign performance and buyer decision-making?

The Data: 

We’ve been given a raw data (CSV files), which contain information about the customers.

The Task: We are tasked with using solely Microsoft Power BI to:

• Connect and transform/shape the data in Power BI’s back-end using Power Query
• Build a relational data model, linking the fact and dimension table
• Design a multi-page interactive dashboard to visualize the data in Power BI’s front-end

The Process:

1. Connecting and Shaping the Data

Firstly, we imported the data into the Power Query editor to transform and clean it. The next process involved:

Removing Duplicates: Duplicate entries were removed from the dataset to ensure accurate analysis.

Handling Null or Missing Values: For some columns, missing values were replaced with defaults or averages. Null values in “key” columns were removed using filters.

Data Type Conversion: Columns were converted to appropriate data types to ensure consistency. Dates were converted to Date type, numerical columns to Decimal or Whole Numbers, and text columns to Text.

Column Splitting and Merging: Several columns were split to separate concatenated information, or merged to create a unified name (such as Customer Full Name).

Standardising Date Formats: All date columns were formatted consistently to facilitate time-based analysis. This step was important for ensuring accurate time-series analysis in Power BI.

Removing Unnecessary Columns: Irrelevant columns were removed to streamline the dataset. This helped focus the analysis on relevant information, reducing memory usage and improving performance.

2. Building a Relational Data Model

We modeled the data to create a snowflake schema.This process involved creating relationships between the dimension and fact tables, ensuring cardinalities were one-to-many relationships.

3. Visualising the Data

The final step of the project was creating a multi-page interactive dashboard, including a range of visuals and KPIs that could serve management and lead to informed decision-making. We used several visuals and tools to demonstrate and visualize the data across the 3 report pages, including KPI cards, line and bar charts, donut charts, 100 % Stacked bar chart, data bars, and Key AI Influencer. We made sure the report was fully interactive and simple to navigate, with icons used to enable filters, cancel filters, and guide users to each report page with ease.

Campaign Performance Report: This is mainly about marketing campaigns performance. We use different cards and icons with these cards to make it look more attractive. We included two column charts to show which campaign has generated more purchases and sales. We have also included two bar charts to display which product was mostly sold and which platform has generated more sales. Furthermore, two 100 % Stacked bar charts were added to show which product was sold through which campaign and which platform was used to purchase the product.

Buyer Composition: This report provides information about customer. This report included many attractive visuals. Two donut and two column charts are added to show buyer personal information effect our marketing campaign like marital status, kids at home, education level and teen at home. KPI Cards are included to show average income, average age and many other. A bar chart is added between average income by each campaign with data bars to show maximum and minimum salary ranges. 100 % Stacked Column chart is added to show how age is corelated with fruits consumption

Purchase Drivers: In this report, two Key AI influencer are added to show how different parameters effects the marketing campaign and which parameter is corelated to increase or decrease in sales.

The Situation: 

The sales analysis on the Superstore dataset is a comprehensive study that aims to analyze the sales performance of a fictional retail company called “Superstore”. The dataset used in this analysis contains information about sales transactions, customers, products, and geographical locations. Our task is to derive meaningful insights into retail operations and customer behavior.

• Track KPIs (sales, revenue, profit, returns)
• Compare regional performance
• Analyze product-level trends
• Identify high-value customers
• Identify Profitable Products
• Category Level Analysis
• Market Level Analysis

The Data: 

The Task: We are tasked with using solely Microsoft Power BI to:

• Connect and transform/shape the data in Power BI’s back-end using Power Query
• Build a relational data model, linking the fact and dimension table
• Create calculated columns and measures with DAX
• Design a multi-page interactive dashboard to visualize the data in Power BI’s front-end

The Process:

1. Connecting and Shaping the Data

Firstly, we imported the data into the Power Query editor to transform and clean it. The next process involved:

Removing Duplicates: Duplicate entries were removed from the dataset to ensure accurate analysis.

Handling Null or Missing Values: For some columns, missing values were replaced with defaults or averages. Null values in “key” columns were removed using filters.

Data Type Conversion: Columns were converted to appropriate data types to ensure consistency. Dates were converted to Date type, numerical columns to Decimal or Whole Numbers, and text columns to Text.

Column Splitting and Merging: Several columns were split to separate concatenated information, or merged to create a unified name (such as Customer Full Name).

Standardising Date Formats: All date columns were formatted consistently to facilitate time-based analysis. This step was important for ensuring accurate time-series analysis in Power BI.

Removing Unnecessary Columns: Irrelevant columns were removed to streamline the dataset. This helped focus the analysis on relevant information, reducing memory usage and improving performance.

2. Building a Relational Data Model

We modeled the data to create a snowflake schema.This process involved creating relationships between the dimension and fact tables, ensuring cardinalities were one-to-many relationships.

3. Creating Calculated Columns and Measures:

Next, we used Power BI’s front-end formula language, DAX, to analyze our relational data model and create several calculated columns (for filtering) and measures (for aggregation), that we could later reference and use when analyzing and visualizing the data.

We used calculated columns to determine whether a Profit Value is Positive/Negative, a Duration Column to calculate shipment time in days, and a Duration in Time column to check whether delivery was on time (Yes/No).

The list of calculated measures is available below:

4. Visualising the Data

The final step of the project was creating a multi-page interactive dashboard, including a range of visuals and KPIs that could serve management and lead to informed decision-making. We used several visuals and tools to demonstrate and visualize the data across the 12 report pages, including KPI cards, line and bar charts, matrices, gauge charts, maps, donut charts, and slicers. We made sure the report was fully interactive and simple to navigate, with icons used to enable filters, cancel filters, and easily guide users to each report page. Features such as drill-through, bookmarks, parameters, and tooltips were also used throughout the dashboard, further enhancing its usefulness and impact on management.

Executive Level Report: The first report page provides a high-level view of Global Super Store’s overall performance. We used card visuals to present Key Performance Indicators such as total sales, profit, quantity sold, and average quantity per order. We also included a line chart to visualize the trending sales and profit from 2018-2021 and highlight long-term performance and presented the number of orders by product category to aid in understanding product sales distribution, and used a further table to display the top 25 customers by profit

Key Insights Report: The second report page provides the important key points. We used card visuals to present % of Profitable Products, % of Profitable Customers, % of loss transactions and % of profitable transactions. We also included a bar chart to visualize shipment duration details and profit by Market, and presented donut chart to displace which category was more profitable and also included a Tree Map chart to Show sales by Order priority, and used further a table to display Sales Person Year on Year Revenue change.

Market Level Report: This report depicts important points about Market. We used Line Chart to Display Total sales by dates on a specific Market, a table to display country level profit, sales and YOY% Change.

Country Level Report: The report provides information about Country Level Analysis. A line chart shows total sales on the specific country and further we included a table that shows state level profit, sales and YOY% Revenue change.

State and City Level Reports: These two pages provides overview of total sales by State and City Level. These two reports have the visual that were in Market and Country Level.

Category and Sub Category Level Report: These two reports displace category and sub category wise sales and profit and further a table is included to show Product wise sales, profit and quantity sold.

Product Level Report: The report is mainly about Product level analysis. Two bar charts were included to show country wise shipment cost and sales. A map chart to show which country has most sales, a donut chart to show product purchased by shipment mode and further a table was included to display city wise sales, profit, discount and quantity sold.

Customer Level Report: This report provides information about customer like how many orders a customer has done? How much sales and profit he has generated for the store. A donut chart to show is this customer a profitable customer or not?

Sales Person Level Report: This report provides detail overview of sales person. We include 5 KPI cards to show total sales, total profit, quantity sold, previous year revenue and current year revenue. A area chart was created to show sales by city for the specific sales person, Further, we have included to donut charts and a bar chart to shows sales by category, city and product name.

Segment Level Report: This report gives information about how much revenue does each segment has generated. We used two gauge chart to display current and previous year profit and further a bar chart to show category wise profit.

The Situation: 

Oodles of Noodles is a Meal Kit Delivery Service provider company. Our task is to derive meaningful insights into retail operations and customer behavior.

• Track KPIs (sales, shipments, profit, reviews)
• Compare regional performance
• Analyze product-level trends
• Identify high-value customers
• Analysis Subscription trends

The Data: 

We’ve been given a collection of raw data (CSV files), which contain information about Meal Kits sold from 2020 to 2022

The Task: We are tasked with using solely Microsoft Power BI to:

• Connect and transform/shape the data in Power BI’s back-end using Power Query
• Build a relational data model, linking the fact and dimension table
• Create calculated columns and measures with DAX
• Design a multi-page interactive dashboard to visualize the data in Power BI’s front-end

The Process:

1. Connecting and Shaping the Data

Firstly, we imported the data into the Power Query editor to transform and clean it. The next process involved:

Removing Duplicates: Duplicate entries were removed from the dataset to ensure accurate analysis.

Handling Null or Missing Values: Missing values were replaced with defaults or averages for some columns. Null values in “key” columns were removed using filters.

Data Type Conversion: Columns were converted to appropriate data types to ensure consistency. Dates were converted to Date type, numerical columns to Decimal or Whole Numbers, and text columns to Text.

Column Splitting and Merging: Several columns were split to separate concatenated information, or merged to create a unified name (such as Customer Full Name).

Standardizing Date Formats: All date columns were formatted consistently to facilitate time-based analysis. This step was important for ensuring accurate time-series analysis in Power BI.

Removing Unnecessary Columns: Irrelevant columns were removed to streamline the dataset. This helped focus the analysis on relevant information, reducing memory usage and improving performance

2. Building a Relational Data Model

Secondly, we modeled the data to create a snowflake schema. This process involved creating relationships between the dimension and fact tables, ensuring cardinalities were one-to-many relationships.

3. Creating Calculated Columns and Measures

Next, we used Power BI’s front-end formula language, DAX, to analyze our relational data model and create several calculated columns (for filtering) and measures (for aggregation), that we could later reference and use when analyzing and visualizing the data.

We used calculated columns like “DaysInTransit” to check Gap Between the Shipment date and the Arrival Date, a “Total” Column to calculate the subtotal, shipment charges plus tax and “ProcessingLag” Column to calculate date difference between ship date and expected ship date

The list of calculated measures is available below:

4. Visualising the Data

The final step of the project was creating a multi-page interactive dashboard, including a range of visuals and KPIs that could serve management and lead to informed decision-making. We used several visuals and tools to demonstrate and visualize the data across the 4 report pages, including KPI cards, line and bar charts, donut charts and a line chart with time forecasting. We made sure the report was fully interactive and simple to navigate, with icons used to enable filters, cancel filters, and guide users to each report page with ease.

Executive Level Report: The first report page provides a high-level view of Oodles of Noodles overall performance. We used card visuals to present Key Performance Indicators such as Total Revenue, Number of Meal Kits sold, total shipments, average reviews. We also included a bar chart to show monthly meal kits sold and a column chart to Revenue generated in each region, we also used gauge chart to show average review and target review, a Tree Map to show Revenue generated Per Subscription Plan and further two KPI Chart to show Current Revenue and Target Revenue and also Current Meal Kits Sold and Target Meal Kits Sold.

Regional Level Report: This Report is mainly about Regional Level Analysis. We used a Bar Chart to show State wise Revenue generated, a gauge chart to show SLA Policy Achievement in Percentage, a table to show Cuisine Type wise meal kits sold and average review for that particular meal kit and Region and further a line chart was added to show monthly revenue generated and upcoming 10 months predicted revenue using Forecasting in Power BI.

Subscription Level Report: This report provides information about Subscription Plan. We used a Bar chart to show Subscription Plan wise Revenue and find out Plan 3 meal kits / 4 serving has generated about 0.8 million dollars of revenue. Two cards are added to show average monthly subscription fee and total meal kits per week sold. A donut chart to show Servings per order and a column chart to display Shipping Fee per Meal Kit and further a table is used to show customer name, active member or not, loyalty member status and early joining date.

Customer Level Report: This report provides gives key points about customers. We used KPI cards to show customer name, meal kits purchased, revenue generated and shipments details. A Donut chart to show active or inactive customer and further again a donut chart to show which Cuisine Type customer has purchased most.