Improving Sales Insights with Power BI

5 min readFeb 26, 2023

Finding the sales analysis of AtliQ Technologies using Power BI

Problem statement
Solution
Data
Task
Data Cleaning
Finding Profit Margin, Sales Quantity and Revenue made in each year/month
Analyzing top 5 customers and product type by revenue
Finding the Revenue Margin Contribution, Profit Margin Contribution and Profit % by each customer
Finding the Revenue Margin Contribution, Profit Margin Contribution and Profit % by each Market
Finding the Revenue trend by years
Analyzing Revenue contribution by customer type
Additional Tasks
Final Report
Insights
What I’ve Learned
References

Tools used: Power BI, MySQL

Problem statement

AtliQ Hardware, with multiple branches across India, provides computer hardware and peripheral manufacturers to its clients. The sales director is encountering difficulties in comprehending the company’s current issues and performance, as sales are decreasing below expectations.

Whenever the director seeks updates from regional managers regarding sales and the market, they tend to sugarcoat the truth and send excessive Excel files, adding to the director’s frustration. The frustration is understandable, as humans often find it challenging to comprehend numbers from Excel files.

Solution

As a data analyst, a solution to address the sales director’s problem at AtliQ hardware would be to BI dashboards that can help with data visualization and analysis. The sales director can use dashboard that pulls in data from various sources, including the company’s sales data, market trends, and regional manager reports. With the dashboard, the sales director can easily see the current state of the business, identify problem areas, and make data-driven decisions.

By having a clear, visual representation of the data, the sales director can also avoid relying on Excel files and sugar-coated reports from regional managers, which can lead to misunderstandings and frustration.

Data

We are provided with the SQL dataset which contains customers, date, transactions, markets, and products tables. We need to provide insights using the data from those tables

Task

Finding Profit Margin, Sales Quantity and Revenue made in each year/month
Analyzing top 5 customers and product by revenue
Finding the Revenue Margin Contribution, Profit Margin Contribution and Profit % by each customer
Finding the Revenue Margin Contribution, Profit Margin Contribution and Profit % by each Market
Finding the Revenue trend by years
Analyzing Revenue contribution by customer type

Data Cleaning

Removed zones which were left blank in market table
Removed sales amount which were less than 0 in sales transaction table
Created new table called Normalized sales price to convert the USD price to INR price in sales transaction table
Removed duplicates from currency column in sales transaction table

Finding Profit Margin, Sales Quantity and Revenue made in each year/month

For all DAX calculations, I’ve created a separate table called as Base Measure and added measures in it.

Sales quantity = SUM('sales transactions'[sales_qty])
---------------------------------------------------------------------------------------
Revenue = SUM('sales transactions'[Normalized_sales_price])
---------------------------------------------------------------------------------------
Total Profit Margin = SUM('sales transactions'[profit_margin])
---------------------------------------------------------------------------------------
Profit margin % = DIVIDE([Total Profit Margin],[Revenue],0)
---------------------------------------------------------------------------------------
Revenue Margin Contribution % = DIVIDE([Revenue],CALCULATE([Revenue],ALL('sales customers'),ALL('sales markets'), ALL('sales products')))
---------------------------------------------------------------------------------------
Profit Margin Contribution % = DIVIDE([Total Profit Margin],CALCULATE([Total Profit Margin],ALL('sales customers'),ALL('sales markets'), ALL('sales products')))

I’ve used Card chart to visualize the values

Analyzing top 5 customers and product by revenue

To display the top 5 customers, I’ve added stacked bar chart and used filtering type Top N to display in chart with x-axis to be revenue and y-axis to be customer and product code