A Computer Hardware Manufacuturer Company faces certain problems with respect to it's Bussiness Sales. Let's say that there is a company named 'A' who supplies computer hardware and peripheral to many clients across India, let's say they have there headquaters in Delhi,India. The sales director of the company is facing so many challenges as the market is growing dynamically. He is facing issues in tracking the sales in regional markets , he's having issues regarding to the insights of the bussiness and therfore he has some regional managers (North India , South india, Central India ) working for his company. Whenever he wants the insights regarding these three region he would approach them and get the details.

The problem is that these conversations are verbal.Even though the managers come up with excel files , its a hugh number of data for the sales director to go through and figure out the need.

The sales director just expects the managers to give a statistical data which are in simpler terms inorder to focus on the weak area that they need to work on inorder to grow their bussiness further.

Project Planning : We will be using AIMS Grid to tackle the problem and find the strategy to solve the problem.Followed by Data discovery , Data cleaning , Data merging and finally generating an interactive dashboard in Power BI.

We are provided with a database named "sales".

The database has five tables namely customers , date , markets , products and transactions.

Building the data model for analysis.

Once the data tables are loaded into the PowerBI , it creates a data model that establish the realtion among differnt tables between the coulmns present in the table. This helps the data analyst to analyse the data in a better rate and come up with efficient results.

1 . The above tables may contain some null values.

2 . It might contain some inappropriate values like negative and zero values.

3 . It might have duplicate records.

4 . It might have terms expressed in differnt formats ( example : the monetary terms can vary based on international sales exports).

Inorder to handle all these issues we need to clean the data first. In simple terms , if we resolve the above issues our data tables will be cleaned and secure enough to produce the correct results during data processing.

SELECT * FROM sales.transactions where transactions.sales_amount<=0;
SET SQL_SAFE_UPDATES = 0;
DELETE FROM sales.transactions WHERE sales_amount <= 0;
select * from sales.transactions;

SELECT * FROM sales.transactions where currency = "USD" or currency = "USD\r";

Table.AddColumn(#"Cleanup currency", "normalize_sales_amount", each if [currrency]="USD#(cr)" then [sales_amount]*75 else [sales_amount])

For our problem statement , we need to analyse the market status in different areas or zones to get the inference on the sales. And based on the sales insight and the trends the sales director will implement some ideas and solution to make the weak areas into stronger ones.

Revenue and Sales Quantity are created and used as the Base Measures inorder to polt differnt UI elements.

Revenue is gonna be the sum of sales_amount column from the tarnasaction table, this gives the Total Revenue across every year.

Sales Quantity is gonna be the sum of sales_qty column from the transaction table, this gives the total number of sales over the years.

This bar chart gives the visulaization of the revenue based on the markets available in the table .

From this we infer that the Revenue obtained in the market Mumbai is 150.08 Million.

This bar chart gives the visulaization of the Number of sales based on the markets available in the table .

From this we can infer that the number of sales occured in Nagpur is 262094.

Inorder to view the revenue based on year, we can use cy_date column in our dashborad.

Inorder to view the revenue based on date, we can use cy_date column in our dashborad.

From the data , we infer that there is a decrement in the revenue by 8.93 Million.

Using filtering Options , we can visualize only the top 5 customers by revenue who contribute more to the sales of the company.

Using filtering Options , we can visualize only the top 5 products by revenue over the sales period.

This revenue trend chart is build vs revenue and date to detect the increment and decrement in the revenue.

Thus in this way an interactive PowerBI can be built.

Previously the engineer team will spend money and time creating these kinds of dashboard. But by using these visualization tools like PowerBI , it becomes easy for the analyst to segregate data and visualize them. And the sales director is much more pleased and clear with the sales insights of his company by just interacting with the PowerBI dashboard.

Lets check where the PowerBI dashboard yeilds the accurate results as in the dataset.

The overall revenue in the year 2020 is 142.22M - this is the number given by PowerBI

Using sql queries we can check the revenue yeild in the year 2020

SELECT SUM(transactions.sales_amount)
FROM sales.transactions
INNER JOIN date ON transactions.order_date = date.date
WHERE date.year = 2020 AND (transactions.currency = 'INR\r' OR transactions.currency = 'USD\r');

By using this interative PowerBi DashBoard the sales director is able to

#1 . track revenue numbers and sales quantity numbers over the years .

#2 . track revenue breakdown by regional states.

#3 . track revenue trends.

Enabling him to finally work on the weak areas and come up with new ideas and grow their bussiness in a effective and efficient manner.

https://dev.mysql.com/downloads/file/?id=520407

https://aka.ms/pbidesktopstore