DATA INSIGHTS (PART-4): POWER BI DASHBOARD AND REPORT FOR SUPERMARKET BRANCH SALES
INTRODUCTION
Dataset description: The growth of supermarkets in most populated cities are increasing and market competitions are also high. The dataset is one of the historical sales of supermarket company which has recorded in 3 different branches for 3 months data. Data analytics methods are easy to apply with this dataset.
Attribute information of dataset: Invoice id: Computer generated sales slip invoice identification number, Branch: Branch of supercenter (3 branches are available identified by A, B and C). City: Location of supercenters, Customer type: Type of customers, recorded by Members for customers using member card and Normal for without member card. Gender: Gender type of customer. Product line: General item categorization groups — Electronic accessories, Fashion accessories, Food and beverages, Health and beauty, Home and lifestyle, Sports and travel. Unit price: Price of each product in $. Quantity: Number of products purchased by customer. Tax: 5% tax fee for customer buying. Total: Total price including tax. Date: Date of purchase (Record available from January 2019 to March 2019). Time: Purchase time (10am to 9pm). Payment: Payment used by customer for purchase (3 methods are available — Cash, Credit card and Ewallet). COGS: Cost of goods sold. Gross margin percentage: Gross margin percentage. Gross income: Gross income. Rating: Customer stratification rating on their overall shopping experience (On a scale of 1 to 10)
Download Dataset: https://www.kaggle.com/aungpyaeap/supermarket-sales
GOAL : This project contains a detailed report and dashboard of supermarket sales for the supermarket branches, We answer some questions about branch sales to get data insights. We also create a dashboard for Branch A supermarket sales.
CONCEPTS LEARNT
(i) How to make Reports on Power BI desktop and publish to Power BIcloud.
(ii)How to pin a Power BI dashboard.
(iii)How to create visualizations in Power BI
OUTLINE
1. Making the report on Powerbi desktop
2. Publish the report to Powerbi cloud
3. Pin a dashboard in Powerbi cloud
- Making the report on Power BI desktop
What is Powerbi desktop?
Power BI Desktop is a free application you install on your local computer that lets you connect to, transform, and visualize your data. With Power BI Desktop, you can connect to multiple different sources of data, and combine them (often called modeling) into a data model.
What is Power BI service or Power BI cloud?
Power BI cloud runs on the internet and allows for effective access to Power BI reports, dashboards, etc. Most users who work on business intelligence projects use Power BI Desktop to create reports, and then use the Power BI service to share their reports with others.
The supermarket sales report contains three sheets which shows different analysis and visualizations for branch A, B and C of the supermarket.
Sheet 1 report
(i) What is the gross income and gross margin for the three branches?
NOTE : to select a visualization tool in powerbi desktop simple click the right pane which contains different charts, and select the appropriate data fields.
We can use a stack bar chart to visualize the gross income and gross margin for the three branches.
(ii) What is the quantity of product line sold in the three branches?
Clustered column chart is used here.
(iii) What is the customer type, rating, invoice ID, date of payment, type of payment, city of purchase and product purchased per branch?
Pivot table is most suitable for this representation
(iv) What is the total price of products sold in the three branches?
We can simply use a card to represent this.
(v) Complete report for sheet 1
Sheet 2 report
(i) Number of the 5% tax of products purchased in the three branches?
We can simply use line and clustered column chart to represent this.
(ii) What is the rating of each product per customers?
Use a line chart
(iii) What are total number of gross income , gross margin percentage , cost of goods(COG), and total price including tax.
Use a card
(iv) Complete sheet2 report
Sheet 3 report
(i) What is the total number of products per branch?
Use a donut chart to represent this.
(ii) What is the quantity and total number of product per city and branch?
Use a Map here.
(iii) Add a simple QnA dialogue(optional)
(iv) Complete sheet3 report
2. Publish report to Powerbi cloud
Now we can publish this report to Power BI cloud , so we can share with everyone and also pin a dashboard from it.
3. Pin a dashboard in powerbi cloud
We can easily pin a dashboard from the report we published, to see information for just branch A supermarket sales. When the dashboard is clicked , it opens the main report for more information.
CONCLUSION
This write-up showed how to get insights from a supermarket sales data, and represent it with appropriate charts, also how to build powerbi dashboards and reports.
WRITER: OLUYEDE SEGUN . A(jr)
Resources used (References) and further reading:
Link to download Powerbi file: https://projectsss.s3.us-east-2.amazonaws.com/supermaket_sales_report.pbix
Link to Part-1 of data insights series: https://juniorboyboy2.medium.com/data-insights-part-1-using-kmeans-clustering-to-classify-data-into-groups-4a467c3c36fa
linkedin profile: https://www.linkedin.com/in/oluyede-segun-adedeji-jr-a5550b167/
twitter profile: https://twitter.com/oluyedejun1
TAGS: #POWERBI #REPORT #DASHBOARD #VISUALIZATIONS #DATAINSIGHTS