search

yoga9 / PowerBI_Sales-Dashboard

The objective of the report is to analyze and present comprehensive insights into sales, profit, orders, profit margin, and various comparisons. It aims to provide a clear understanding of key performance indicators and trends using Power BI.
0 stars 0 forks source link

readme

Sales Dashboard

The Objective of the Sales Dashboard / Business Problem:

The objective of the report is to analyze and present comprehensive insights into sales, profit, orders, profit margin, and various comparisons. It aims to provide a clear understanding of key performance indicators and trends using Power BI. The report objectives can be summarized as follows:

1) Calculate Total Sales: Calculate and display the total sales value for the selected period, allowing users to understand the overall revenue generated. 2) Calculate Profit: Calculate and visualize the total profit achieved based on the sales data, providing insights into the financial performance. 3) Analyze Orders: Analyze the number of orders placed during the selected period, helping to identify sales patterns and order trends. 4) Calculate Profit Margin: Calculate and visualize the profit margin percentage, enabling users to assess the profitability of products or services. 5) Compare Sales by Product with Previous Year: Compare sales performance for each product between the selected period and the previous year, highlighting growth or decline in sales. 6) Compare Sales by Months with Previous Year: Compare sales performance across different months between the selected period and the previous year, identifying regions with significant changes. 7) Display Top 5 Cities: Present a visualization showcasing the top 5 cities based on sales, allowing users to quickly identify the most lucrative locations. 8) Compare Profit by Channel with Previous Year: Compare profit generated by each channel between the selected period and the previous year, indicating improvements or challenges in profitability. 9) Create Bookmarks to Analyze Sales/Profit by Customer and Compare with Previous Year as a Hide/Show Visuals with the help of Bookmark Actions & Buttons: Analyze sales/profit data by customer, highlighting the performance of individual customers and comparing it to the previous year. 10) Create Slicers for Date, City, Product, and Channel: Enable users to interact with the data by providing slicers for selecting specific dates, cities, products, and channels, allowing for dynamic filtering and personalized analysis.

Steps to follow for an end-to-end Power BI Project:

1) Gather Data - Load data into Power BI Desktop, dataset is a Excel file.

2) Power Query – Data Extract, Transform & Load Power Query Editor in Power BI is a powerful tool for data cleaning and transformation. Used it to Clean and transform the data to make it suitable for analysis. This may involve removing duplicates, handling missing values, merging datasets, or creating calculated columns.

3) Create a Date Table - To work with Data Analysis Expressions (DAX) time intelligence functions, there’s a prerequisite model requirement: Code for Creating Date Table in Power BI:

DAX DateTable = 
ADDCOLUMNS (
    //CALENDAR(DATE(2020,1,1), DATE(2024,12,31)),
    CALENDARAUTO(),
    "Year", YEAR([Date]),
    "Quarter", "Q" & FORMAT(CEILING(MONTH([Date])/3, 1), "#"),
    "Quarter No", CEILING(MONTH([Date])/3, 1),
    "Month No", MONTH([Date]),
    "Month Name", FORMAT([Date], "MMMM"),
    "Month Short Name", FORMAT([Date], "MMM"),
    "Month Short Name Plus Year", FORMAT([Date], "MMM,yy"),
    "DateSort", FORMAT([Date], "yyyyMMdd"),
    "Day Name", FORMAT([Date], "dddd"),
    "Details", FORMAT([Date], "dd-MMM-yyyy"),
    "Day Number", DAY ( [Date] )
)

4) Create Data Model in Power BI Desktop - Design and create a data model that represents the relationships between different tables in data. This step is crucial for accurate analysis and visualization.

5) Develop Reports in Power BI Desktop - Use the Power BI Desktop application to create reports based on the data model. Add visualizations such as charts, tables to represent the data effectively. Apply filters, slicers, and drill-through functionalities to allow users to interact with the data.

Implementing DAX Calculations:

Use Data Analysis Expressions (DAX) to create calculated columns, measures, and calculated tables to perform complex calculations and aggregations. DAX is a powerful formula language that allows to manipulate data within Power BI.

//Measures Total Sales
Sales = SUM(Sales_Data[Sales])

//Measures Previous Year Toal Sales
Sales PY = CALCULATE([Sales], SAMEPERIODLASTYEAR(DateTable[Date]))

//Diffrence Between Current Year Sales & Previous Year Sales
Sales vs PY = [Sales] - [Sales PY]

//Percentage Increase or Decrease in sales year on year (YOY%)

 Sales vs py % = DIVIDE([Sales vs PY],[Sales],0)
 Products Sold = SUM(Sales_Data[Order Quantity])
 Profit = SUM(Sales_Data[Profit]) 
 Profit LY = CALCULATE([Profit], SAMEPERIODLASTYEAR(DateTable[Date]))
 Profit Vs LY = [Profit]- [Profit LY]
 Profit vs LY % = [Profit Vs LY]/[Profit]
 Profit Margin = DIVIDE([Profit],[Sales],0)
 Total Cost = SUM(Sales_Data[Total Cost])

Snapshot of Sales Dashboard (PowerBI Desktop)

Sales Dashboard-1

Snapshot of Custom Tooltip - Hide

Sales By Product and Comparing it with last year’s Sales visual have the capabilities to view, Custom tooltip page it shows Profit, Profit Margin, Sales of top 5 Suburb as a single tooltip view.

Custom ToolTip

Snapshot of Detail Page - Hide

With the help of the Drill through features in Power BI to view the Detailed page. It’s a fantastic approach to obtain a more detailed look at the entity data you choose for your Drill through the filter.

Detail Page-1

Conclusion of Power BI Sales Dashboard Project:

Conclusion for the year 2019:

1. Sales decreased by more than 10%
2. There is a drop in sales of all the top 7 Products
3. 4 Customers are leading to a drop in sales
4. The profit margin in the Export channel is higher