Unlocking Business Insights: A Deep Dive into Superstore Sales with Microsoft Power BI: Case Study
Installing Power BI: A Simple Guide
Power BI is a powerful tool for data analysis and visualization. Follow these simple steps to install it on your computer.
Step 1: Download Power BI Desktop from the link below.
Open your web browser and go to the official Power BI download page: Power BI Download. Click on the “Download free” button. This will start the download of the Power BI Desktop installer.
Step 2: Run the Installer
Once the download is complete, locate the installer file (usually named “PBIDesktopSetup.exe”) and double-click on it to run the installation. The installation wizard will appear. Follow the on-screen instructions to proceed with the installation. You can choose the default settings unless you have specific preferences.
Step 3: Launch Power BI Desktop
After the installation is complete, you can find the Power BI Desktop icon on your desktop or in the Start menu. Double-click the Power BI Desktop icon to launch the application.
Step 4: Sign In or Sign Up
When you first launch Power BI Desktop, you may be prompted to sign in with your Microsoft account. If you don’t have one, you can sign up for free. Signing in allows you to access additional features and services, but you can also use Power BI Desktop without signing in.
The Superstore Sales Dashboard
The Superstore Sales Dashboard powered by Power BI opens a window into the intricate landscape of business performance. Leveraging the robust capabilities of Power BI, this dashboard serves as a dynamic tool for insightful analysis of sales data. It compiles and visualizes key metrics, providing a comprehensive overview of the Superstore’s sales performance.
Image -1
Designed with user-friendly features, the dashboard offers an intuitive interface for users to explore and understand sales trends, patterns, and outliers. Through strategic visualizations, such as charts, graphs, and tables, it transforms raw data into actionable insights. By incorporating Power BI functionalities like data transformation, type adjustments, and error checks, the dashboard ensures data accuracy and reliability. Additionally, the seamless integration of Power Query and Power View enhances the overall analytical experience, empowering users to extract meaningful conclusions from the Superstore sales data
Project Overview:
I conducted a comprehensive analysis using Superstore Sales data, focusing on the creation of a sales dashboard. The primary objective was to present a comparative study of two years’ worth of data for analysis. Key Performance Indicators (KPIs) such as orders, sales, profit, and shipped days were incorporated into the dashboard. Additionally, four filter boxes were implemented.
The dashboard features two line charts illustrating the sales and profits evaluation for 2019 and 2020 on a monthly basis. Notably, these charts display data from both years in a single view. Three donut charts on the side provide insights into metrics like Sales by Payment, Sales by Region, and Sales by Segment.
Furthermore, a stack column chart visualizes sales by ship mode, sales by category, and sales by sub-category. A specific measurement is also included with the help of map chart, to showcase profit and sales by state. The overall goal is to provide a detailed representation of various metrics through visualizations and enhance the understanding of Superstore Sales data.
I have also performed sales forecasting. It is very exciting to learn about sales forecasting in Power BI.
Objective: To contribute to the success of a business by utilizing data analysis techniques, specifically focusing on time series analysis, to provide valuable insights and accurate sales forecasting.
Description-The objective can be broken down into the following components:
1. Dashboard creation: identify the KPIs design an intuitive and visually appealing dashboard, and interactive visualizations and filtering capabilities to allow users to explore the data at various level of granularity.
2. Data analysis: provide valuable insights to business entities regarding the effectiveness of their sales strategies through visualization and charts.
3. Sales Forecasting: leverage historic data and apply time series analysis to generate sales forecasts for next 15 days.
4. Actionable insights and recommendations: end goal is to share valuable insights and actionable information that drive strategic decision-making and support the supermarkets goal for growth, efficiency, and customer satisfaction.
About Data: Our data is saved in a CSV file. This file has various columns, and information is kept in these columns. Inside this file, you can find all the important details about the business, such as customer information, order date, shipping method, country, city, state, sales, profit, category, sub-category, and more.
Import & Clean Data: We will retrieve our data by selecting the ‘Get Data’ option. In the dropdown for bringing data from storage, we will choose ‘Text/CSV’ to import our data into Power BI. Assuming you’ve got the Power BI Desktop app and some familiarity with it, here’s a straightforward method: use the ‘Get Data’ button in Power BI Desktop. This button allows you to bring in data from various places, whether it’s stored on your computer or in the cloud. For this case study, the dataset is a CSV file (located into my computer. The data table can be selected and imported into the Power Query Editor for some data transformations.
Transform Data: First of all, we won’t load the file; instead, we will click on ‘Transform Data’ to refine our dataset in Power Query. We will examine the data types in each column, checking for integer, character, or date formats. If necessary, we can adjust the data types to fit our needs by right-clicking on the column and selecting ‘Change Type.’ Data types are highlighted in yellow. Using the arrow, we can inspect the quality and errors in the column; in our case, both error and quality are 0, indicating no significant errors. However, we still need to double-check all the columns.
Image -2
We can see that there is an error of #NA in our Returns column. I will right click on it and go to replace value and replace #NA with 0.
Image -3
There are two more columns with empty values. We will select both columns and, after right-clicking, remove them from the dataset.
Image -4
Our data is clean now. Now Close & Apply for doing further analysis.
Visualization to create Insights:
We can view all the columns of the loaded datasets displayed on the right side of our Power BI page. To create a dashboard, the first step is to bring a chart from the visualization section, and initially, we will use the clustered bar chart. Upon clicking the clustered bar chart, a blank bar chart will appear. Now, in this chart, we will place the category on the y-axis and sales on the x-axis.
Sales by category
Image -5
Next, to set the canvas background, simply click anywhere on the page, and then navigate to the “Format” menu. Within the format options, locate the canvas background setting. Once selected, choose an image from the available options in the image section. Enhance the aesthetics by adjusting the transparency level to 0%.
Now, with the visuals of your chart customized to your preferences, explore the “Visualization” section to find a myriad of options for arranging your chart. You can fine-tune settings such as font size, color, headers, titles, and axis configurations (x-axis and y-axis). Dive into the various options to tailor your chart according to your specific needs and preferences.
Observation: Our chart for the category is now prepared, illustrating a graph depicting the sales of office supplies, technology, and furniture. Clearly, we can observe from the chart that the total sales for all three categories are as follows: office supplies amount to 0.64 million, technology to 0.47 million, and furniture to 0.45 million.
We are going to make two more charts, for this we will do the same steps
Using the same steps, we’re going to create two more charts. To do this, we’ll copy the current chart using Ctrl+C and paste it using Ctrl+V. Then, go to the “Add” option, change the category, and put the subcategory in place on the Y-axis. Once we’ve made these changes, we might notice there are many values in the subcategories, and it can be hard to make sense of all of them. To make things clearer, we want to focus on the top 3 subcategories. To do this, we’ll use the filter option. Select the subcategory in the filter, and choose “TOP N.” It will then ask for the number of values we want to see. Let’s enter 3, so we only see the top 3 subcategories. Below that, there’s an option called “BY values.” Here, we specify that we want the top 3 subcategories based on sales.
Image -6
After making these selections, apply the filter. This will help us concentrate on the most important information and make our charts more straightforward and easier to understand.
Sales by sub-category
Image -7
Observation: In this clustered chart, we can see the top three sub-categories based on sales: phones at 197k, chairs at 182k, and binders at 175k.
Sales by ship mode
Applying the same methods we will make graph on sales by ship-mode.
Image -8
Observation: Now, analyzing the ship mode based on the values above is straightforward. Standard class records 330k, second class 112k, first class 82k, and same day 30k. The higher values for standard class can be attributed to its affordability, almost being free. In contrast, the lower value for same day (30k) is due to customers having to pay extra for expedited shipping.
Sales, year on year comparison:
Now we will compare the data of two years 2019 and 2020 on the basis of sales year by year.
Image -9
To accomplish this, I will utilize an area chart to compare the sales data of 2019 and 2020. I will click on the area chart icon, necessitating the inclusion of both the sales and order date fields for effective visualization. Consequently, I have designated the order date for the x-axis (months) and sales for the y-axis. In the legend section, I will incorporate the order date, selecting the year to provide a comprehensive representation. And with the help of legend we can show the values of two years in a single chart.
Chart formatting:
Formatting a chart to match an existing one is straightforward. Simply click on the existing chart, navigate to the “Home” tab where you will find the “Format Painter,” click on it. Afterward, go to the newly created chart that you wish to format, click on it, and your newly created chart will be formatted similarly to the existing chart.
Image -10
By following the discussed process above, we will create two graphs: one for the year-by-year comparison of sales and another for the year-on-year profit. For the second graph, we only need to replace sales with profit.
Observation:
The values can be observed by hovering the mouse over the desired month, and the corresponding values for both years will be displayed.
MAP: We are now going to use a map. Click on “Map” under visualization, and a blank map will appear on the page. Place the state in the location field, the sum of sales in the bubble field, and the sum of profit in the tooltip. By doing this, a map will be generated where the bubble size represents maximum sales, and smaller sizes represent minimum sales.
Image -11
Observation: To view the values, hover the mouse over the desired state, and the corresponding sales and profit values will be displayed. For example, when I tried to see the values for New York, it was evident that the sum of sales is 186,748.097, while the sum of profit is 41,012.02.D
Donut chart:
Now, I am going to visually represent the sum of sales categorized by payment mode (card, online, or cash on delivery), sum of sales segmented by categories (home office, consumer, and corporate), and sales distributed across regions (north, south, east, and west). To convey this information effectively, I will employ a donut chart. Creating a donut chart involves simply selecting the donut chart option and adding the required fields into the respective chart components. For instance, to visualize the sum of sales by segments, I will include “segment” in the legend and the sum of sales in the values field. Similarly, two more donut charts can be created for the sum of sales by payment mode and sales by region using the same process. This approach ensures a clear and concise representation of sales distribution based on different parameters.
Image -12
Observation:
1.Payment Mode Insights: Cash on Delivery is the preferred payment mode, representing 43% of sales. Online transactions account for 35%, while card payments make up 22%. The popularity of Cash on Delivery indicates a notable preference for this payment method among customers.
2.Segment Analysis: The majority of sales come from the consumer segment, constituting 48%, followed by corporate (33%), and home office (19%). This suggests a significant consumer-driven market
3. Regional Sales Breakdown: Sales distribution across regions reveals that the western region contributes the most, standing at 33%, followed by the eastern region at 29%. The central region accounts for 22%, and the southern region makes up 16% of total sales. This implies that the western and eastern regions are key contributors to overall sales.
Conclusion: the consumer segment dominates sales, and there is a clear preference for Cash on Delivery as the payment mode. Geographically, the western and eastern regions play pivotal roles in contributing to the overall sales figures.
Filters:
We will employ a slicer as a filter mechanism to refine our dashboard visualization. To do this, we’ll access the visualization section and incorporate the slicer chart, then add the “region” field to the slicer. To enhance the visual appeal, I will modify the slicer style from vertical to tile and adjust its size to optimize space on the dashboard.
The regions available in the slicer include north, south, east, and west. Upon selecting a specific region, the entire dashboard’s visualization will dynamically adjust to display data pertinent to the chosen region. This use of a slicer as a filter provides a streamlined and interactive approach, allowing users to focus on specific geographical areas and gain insights accordingly.
Image -13
DAX Query:
Here, our aim is to calculate the average number of days it takes for shipments to deliver products to their destination. Since there is no dedicated column for this information, we will employ a Data Analysis Expressions (DAX) query to derive the required metric.
To initiate this process, we will navigate to the data view section, located on the left side of the Power BI dashboard. Within the Table Tools section, we will find the “New column” option. Subsequently, we will access the query box to write the DAX query. Reference to the image will guide us in accurately formulating the query for calculating the average delivery time. This step is crucial for enhancing our understanding of the shipment timeline and improving logistics efficiency.
Image -14
AvgDelivery =DATEDIFF(‘SuperStore_sales’[OrderDate],’SuperStore_sales’[ShipDate], DAY))
Date Difference Calculation:
The ‘Datedif’ function is used to find the difference in days between the ‘OrderDate’ and ‘Ship Date’ in the ‘SuperStore_sales’ table.
Image -15
We will find new column with the name of AvgDelivery into our dataset.
Key Performance Indicators (KPIs):
To incorporate Key Performance Indicators (KPIs) into our visualization, we’ll utilize the card option available in the visualization section. The card serves as a straightforward display for aggregate values, providing a concise representation of key metrics. We have the flexibility to adjust the size of the card as per our specific requirements, ensuring optimal presentation within the dashboard layout.
Image -16
Additionally, for enhanced clarity and context, we can include a title for the card by accessing the general section. This title allows us to provide a brief description or label to guide users in understanding the significance of the displayed aggregate values. The card feature proves to be a versatile tool in succinctly showcasing critical KPIs and facilitating a more informed interpretation of the data within the dashboard.
Observation: In this dataset, we have the following key metrics:
1. Orders: — There are a total of 22,000 orders recorded.
2. Sales:-The cumulative sales amount to 1.6 million units in the given period.
3. Profit:-The total profit generated from these sales is 175,000 units.
4. Ship Days:-The average number of days it takes for shipments to be delivered is 10. This value has been calculated using a DAX query, indicating the average delivery time for the orders in your dataset.
Sales Forecasting:
Forecasting in Power BI is a powerful feature that enables users to predict future trends and values based on historical data. Utilizing various forecasting options, users can apply predictive analytics to gain insights into potential outcomes, aiding in decision-making processes. Power BI offers forecasting tools that allow for the creation of predictive models using time-series data, helping users identify patterns, trends, and potential anomalies. By configuring parameters such as units, forecast length, seasonality, and confidence intervals, analysts can tailor forecasts to suit their specific needs. The resulting visualizations provide a clear representation of predicted values, offering a valuable tool for strategic planning and data-driven decision-making within the Power BI environment.
Image -17
To initiate the process of forecasting in Power BI, we will start by creating a new page. This can be achieved by clicking on the plus sign (+) located below the existing pages. Once clicked, a new page will be generated, providing a fresh canvas for our forecasting visualizations.
For the forecasting analysis, we’ll utilize a line chart with a time series perspective. To do this, I have selected the ‘OrderDate’ field in Power BI. Subsequently, I clicked on the ‘Line Chart’ option from the visualization section. On the chart, the ‘OrderDate’ is placed on the x-axis to represent time, while the sum of sales is plotted on the y-axis.
To enhance the granularity of our data, we then right-clicked on the x-axis and selected ‘OrderDate’ from the drop-down menu. This action ensures that the data is presented at a daily level, allowing for a more detailed and nuanced analysis of sales trends over time.
Additionally, I have included a chart showcasing the top 10 performing states. We have previously covered the process of creating such charts, making it a straightforward task. This chart provides a quick and accessible visual representation of the performance of the top states, adding valuable insights to our analysis.
Image -18
- Accessing Forecasting Options-Navigate to the Visualization section in Power BI.
2. Locating the Forecasting Icon-Look for the icon labeled “Add further analysis to your visual” in the Visualization section.
3. Opening the Drop-down Menu-Click on the mentioned icon to reveal a dropdown menu with additional analysis options.
4. Enabling Forecasting-Within the dropdown menu, activate the forecasting option. This action will introduce various parameters for configuring the forecast.
5. Configuring Forecast Settings-Customize the forecast settings based on your requirements.
These settings include: Units-Choose the time unit for the forecast; in this case, I have selected days. Forecast Length-Set the length of the forecast, and I have chosen 15 days. Ignore Last-Optionally choose to ignore the last data point for more accurate forecasting; I have chosen to ignore 1 data point. Seasonality-Auto, indicating automatic detection of seasonal patterns. Confidence Interval-Select the desired level of confidence for the forecast, and I have chosen 95%.
6. Applying the Forecast-
After configuring the forecast parameters, click on the apply button to initiate the forecasting process. This straightforward process allows you to easily incorporate forecasting into your visualizations in Power BI, providing valuable insights into future trends based on your specified settings.
Image -19
Adding the Zoom Slider:
Following the addition of the zoom slider, which is a tool in Power BI to focus on specific sections of a graph, you gain the capability to observe forecasted data with greater detail.
Observation: after incorporating the zoom slider and configuring a 15-day forecast, you can precisely examine forecasted values for specific dates. For example, on Saturday, January 09, 2021, the forecasted value is 4227.61, with an upper bound of 8,575.50 and a lower bound of -120.28, providing a range of confidence in the forecasted outcome.
Sum of Sales by State:
Additionally, I have included a chart showcasing the top 10 performing states. We have previously covered the process of creating such charts, making it a straightforward task. This chart provides a quick and accessible visual representation of the performance of the top states, adding valuable insights to our analysis.
Image -20
Basic insights
· Payment Mode Dynamics:
Our examination of payment modes revealed a predominant use of cash on delivery, with comparatively fewer transactions conducted through cards. This insight suggests an opportunity for targeted promotions, especially for credit card users. Running exclusive offers on credit card payments could potentially incentivize customers to opt for this mode, thereby diversifying our payment channels.
· Seasonal Sales Peaks:
Notably, our analysis indicates distinct peaks in sales and profit during October and December. Recognizing these months as top performers allows us to strategically plan marketing campaigns, promotions, or inventory management to capitalize on the heightened consumer activity during these periods.
· Order and Revenue Overview:
Over the span of two years, our dataset encompasses a substantial volume of 22,000 orders, resulting in a commendable sales figure of 1.6 million dollars. The corresponding profit earned stands at an impressive $175,000. This holistic view provides a comprehensive understanding of the scale and financial impact of our operations, guiding future business strategies and goals.
In summary: these basic insights not only shed light on the existing trends in payment behaviors and seasonal fluctuations but also present a holistic overview of our business performance. Leveraging these insights strategically can lead to targeted initiatives that enhance customer engagement, capitalize on peak seasons, and optimize overall business outcomes.