Global Superstore Analysis

An end to end Power-BI dashboard project

5 min readNov 19, 2023

Introduction

The Global Superstore dataset provides a comprehensive glimpse into the retail landscape, encapsulated within an extensive Excel file featuring three distinct sheets: ‘Orders,’ ‘People,’ and ‘Returns.’ Spanning a robust four-year period from 2012 to 2015, this dataset encapsulates a vast array of order-related information, offering insights into retail operations across an impressive 147 countries worldwide.

PROJECT GOAL

The primary objective of this project is to conduct a comprehensive analysis of the dataset, focusing particularly on sales and profit metrics. To achieve this goal, we will employ data filtering techniques to refine and streamline the dataset, ensuring that only the most relevant columns are considered in our analysis.

By honing in on specific columns, we aim to extract meaningful insights and patterns that are integral to understanding the dynamics of sales and profitability within the dataset. This meticulous approach not only allows for a more focused analysis but also facilitates a deeper exploration of the factors influencing sales and profit outcomes.

Project Outcome

We will able to answer all these questions using Power BI visuals.

(1) Find out the total sales value of the transaction every year.

(2) Count the number of product sale in each year.

(3) What is the Average Delivery Days of global superstore?

(4) How many orders returned in each year?

(5) Show me the distribution between segment by sales.

(6) Show me the distribution of sales by market.

(7) Top 10 customer by Profit.

(8) Top 5 Profitable product

(9) Top 5 Loss products.

Dataset

You can find the dataset from following link

Dataset

Step-1

Connecting Databases(EXCEL,SQL,WEB,ETC)

We can connect or import data into Power BI by various methods like excel,sql,web etc ,so now in our case we are importing our data from excel sheet. The excel sheet download link is provided above .After downloading the excel sheet open Power BI application and follow the following steps as shown in the images to import excel data into Power BI

After that another window will open and then you have to select the excel workbook which was downloaded following the above link , and after that it will show the tables present in that sheet so select all the tables as shown in below image and click on load .

Now our data is loaded in Power BI .

You can view the loaded data by clicking on left most corners table like icon.

Step-2

Cleaning and Transforming Data

In this step we go through our data see how our data is present like how many missing values, null values and also creating measures which will be used in visualization .

In our data in Return table and People table have not proper heading for the columns as we can see the the while importing data from excel the column name shifted into record filed so to make this record as our column heading we need to make it as column heading. To do that we need to first transform our data to perform these editing to our data using power query and many other techniques.

To do that under HOME tab click on transform data .

New window will open now we will make change in our table as we know we have to make our 1 st row as column to do that

Click on “Use First Row as Headers” under Home tab

Now your first row is your column heading .

Perform same step to Return table .

Now lets create some measures and add some columns based on certain criteria which will be helpful in our visualization part.

a) . We have the columns order_date and deliver_date but we need another column which will show us how many days is taken to deliver particular item so to do that click on transform data option under home tab after that new window will open now under Add Column tab click on custom column tab and now pop will open fill the values in it as shown in below image which will calculate date difference

Add this formula and click on ok ,now your new column is created

And change the datatype of that column to whole number to do that under Home tab go to Data type option under transform tab and set it to whole number.

b) We have date column in our dataset but we have to show the sales by year so what we can do is to extract year from date column so it will be better for use to filter the values according to the year. To do that we have to go on Custom column option then type the formula from the below image

Now new column is added in dataset for year.

Now we have performed operation on the dataset now under Home tab click on Close and apply option . All the changes now will be reflected in your original dataset in Power BI.

Step-3 Building a Dashboard or a Report

The above dashboard is the answer to the all the questions below :