Analytics Vidhya
Published in

Analytics Vidhya

Powerbi desktop report for supermarket branch sales

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

  1. 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.

Visualization and data field panes in powerbi desktop

We can use a stack bar chart to visualize the gross income and gross margin for the three branches.

stack bar chart to visualize the gross income and gross margin

(ii) What is the quantity of product line sold in the three branches?

Clustered column chart is used here.

Clustered column to visualize the quantity of product line sold in the three branches

(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

Pivot table for the customer type, rating, invoice ID, date of payment, type of payment, city

(iv) What is the total price of products sold in the three branches?

We can simply use a card to represent this.

Card for total price of products sold in the three branches

(v) Complete report for sheet 1

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.

line and clustered column chart to represent 5% tax of products purchased in the three branches

(ii) What is the rating of each product per customers?

Use a line chart

Line chart showing the rating of each product purchased by customers

(iii) What are total number of gross income , gross margin percentage , cost of goods(COG), and total price including tax.

Use a card

Card showing total number of gross income , gross margin percentage , cost of goods(COG), and total price including tax

(iv) Complete sheet2 report

Complete sheet2 report

Sheet 3 report

(i) What is the total number of products per branch?

Use a donut chart to represent this.

donut chart showing the total number of products per branch

(ii) What is the quantity and total number of product per city and branch?

Use a Map here.

Map showing the quantity and total number of product per city and branch?

(iii) Add a simple QnA dialogue(optional)

Question and answer field

(iv) Complete sheet3 report

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.

Click on publish in powerbi desktop
Log in to powerbi cloud to see published report

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.

highlight branch A and click on the pin icon to pin a dashboard for branch A
Add the name of the dashboard
Dashboard containing information about supermarket sales in branch A alone.

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

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store