Power Bi Project End-to-end | Sales Dashboard | Beginners | Complete Project | Resume - 2024

Name: Power Bi Project End-to-end | Sales Dashboard | Beginners | Complete Project | Resume - 2024
Uploaded: 2023-07-24T07:30:17.000Z
Duration: 2 h 12 min 44 s

Introduction to Power BI Sales Dashboard Tutorial

In this section, the speaker introduces the tutorial on building a powerful Power BI sales dashboard and report. The tutorial is designed for both beginners and experienced users of Power BI.

Objectives of the Sales Dashboard

The tutorial aims to address 10 key points that a sales dashboard should answer.

These points include calculating total sales, profit, analyzing orders, comparing sales by product and month with previous years, displaying top 5 cities, comparing profit by channel with previous years, analyzing sales by customer, and creating slices for date, city, product, and channel.

Gathering Data

The first step in building the project is to gather all necessary data from various sources such as databases, spreadsheets, or web services.

For this tutorial, an Excel file named "Sales Analysis Report" is used as the data source.

The Excel file contains four tabs: Sales Order, Customers, Region, and Products.

Each tab contains relevant information such as order dates, customer names, product descriptions, quantities ordered, unit prices/costs/profits.

Loading Data into Power BI

After gathering the data, it needs to be loaded into Power BI for analysis.

Power Query Editor in Power BI Desktop is used for data cleansing and transformation to make it suitable for analysis.

Loading Data into Power BI

This section focuses on loading the gathered data into Power BI using Power Query Editor.

Steps:

Open Power BI Desktop.

Load the Excel file containing the data.

Use Power Query Editor to cleanse and transform the data for analysis.

Building Data Model in Power BI

This section covers the process of building a data model in Power BI by connecting different tables and creating relationships.

Steps:

Connect the relevant tables from the loaded data.

Create relationships between the tables based on common keys or indices.

Ensure that the relationships are correctly established to enable accurate analysis and visualization.

Creating Date Table using DAX

This section focuses on creating a date table using DAX (Data Analysis Expressions) in Power BI.

Steps:

Use DAX functions to create a date table with relevant columns such as dates, months, years, etc.

The date table will be used for time intelligence calculations and analysis in the sales dashboard.

Building Sales Dashboard and Report

This section covers the process of building the actual sales dashboard and report in Power BI.

Steps:

Start by creating slicers for filtering data based on date, city, product, and channel.

Create key performance indicator (KPI) measures to track important metrics such as total sales, profit margin, etc.

Design and create visuals such as bar charts, line charts, etc., to represent sales data visually.

Apply formatting options to enhance visual appeal and readability of the dashboard.

Utilize conditional formatting techniques to highlight specific data points based on predefined rules.

Implement tooltips for providing additional information when hovering over specific visuals.

Publishing Report to Power BI Service

This section explains how to publish the created report from Power BI Desktop to Power BI Service for sharing and further analysis.

Steps:

Publish the report from Power BI Desktop to Power BI Service.

Access the report in Power BI Service and explore its features.

Share the report with other users and collaborate on it.

Download the report in PDF format for offline viewing.

Conclusion and Additional Resources

In this final section, the speaker concludes the tutorial and provides additional resources for further learning and practice.

Summary:

The tutorial covered an end-to-end process of building a powerful Power BI sales dashboard and report.

It included steps such as gathering data, loading it into Power BI, building a data model, creating a date table using DAX, designing the dashboard, publishing to Power BI Service, and sharing the report.

Additional Resources:

A post on datatables.com provides detailed information about each step followed in the video tutorial.

The post includes downloadable resources such as data sets, images, and PBIX files used in the tutorial.

Users can follow along with the video by creating their own dashboard using these resources.

Timestamps are approximate and may vary slightly depending on the source video.

Excel Data Transformation in Power BI

This section explains how to transform data from an Excel file using Power Query Editor in Power BI.

Transforming Data in Power Query Editor

Click on the four tabs in the Excel file and select "Transform Data" to open the Power Query Editor.

In the Power Query Editor, manipulate data by changing column names, adding custom columns, and modifying column types.

Use features like adding columns, transforming rows, filling values, replacing values, splitting columns, and more.

View column quality to check for valid data and empty rows.

Analyze data using column distribution and profile views.

Loading Data into Power BI

This section covers loading transformed data into Power BI and creating a date table.

Loading Transformed Data

Close and apply the changes made in the Power Query Editor to load the transformed data into Power BI.

Verify that the data is loaded correctly by checking the report view and data view.

Creating a Date Table

Create a date table to work with index time intelligence functions.

Copy the code provided in the transcript to create a date table.

In Power BI Desktop's formula bar, paste the code for creating a date table.

Switch back to report view to see that an automatic date table has been created.

Building Data Model in Power BI Desktop

This section explains how to build a data model in Power BI Desktop by establishing relationships between tables.

Establishing Relationships

Access model view in Power BI Desktop.

Delete any existing relationships automatically created by Power BI.

The transcript does not provide further details on building relationships or defining keys/hierarchies.

Connecting Tables and Creating Star Schema

In this section, the speaker explains the process of connecting four tables with the sales table to create a star schema. The tables include customer name index, delivery region, product description ID, and separate sample columns in different tables.

Connecting Tables

Connect the delivery region index from the region table to the sales table to establish a one-to-many relationship.

Connect the customer index from the customer name index in the sales table to establish a relationship.

Use "Manage Relationship" or "New" option to create relationships between tables.

Connect the product description index from the product table to the sales table with a one-to-many relationship and single cross-filter direction.

Completing Data Model for Power BI Report

This section focuses on completing the data model for a Power BI report. The speaker mentions connecting tables and creating relationships using indexes. They also mention completing the data model by adding an order date column.

Completing Data Model

Create a relationship between tables using relevant indexes such as delivery region, customer name, and product description.

Add an order date column to complete the data model for the Power BI report.

Creating Report Background

Here, we learn about creating a report background using PowerPoint. The speaker demonstrates how to import images and insert shapes to design a background for our Power BI report.

Creating Report Background

Download an image from the internet or use pre-downloaded images.

Import images into PowerPoint.

Insert images into Power BI as background elements.

Insert shapes like rectangles and customize their color and size.

Creating Slices for Date, City, Product, and Channel

This section covers creating slices (visual filters) for date, city, product, and channel in the Power BI report. The speaker demonstrates how to add slices and customize their settings.

Creating Slices

Right-click on the canvas in Power BI Desktop and select "Add visual" to add a slice.

Change the visual type to "Slices."

Add data for each slice category (date, city, product, channel).

Customize the settings of the slices using the format pane.

Adjust style types, background colors, font colors, and other properties of the slices.

Sorting Months in Slicer

In this section, we learn how to sort months in a slicer. The speaker demonstrates creating a sort column and formatting it to display months in the desired order.

Sorting Months

Create a new column called "Month Sort Format" in the date table.

Use formulas to format dates as "yyyy.mm" (e.g., January 2017 becomes 2017.01).

Sort the month sort column by month name.

Creating Slices for Sorting Data

In this section, the speaker demonstrates how to create slices to sort data in a Power BI report.

Creating Date Slice

Clicking on a column allows sorting by that column.

Change the title of the slice to "Date".

Creating Other Slices

Copy and paste the existing visual to create additional slices.

Remove unnecessary elements from each slice.

Create slices for City, Product, and Channel.

Adjusting Slice Layout

Select all slices and change their layout.

Adjust width to 160.

Format slices to distribute horizontally and align middle.

Creating Text Measures

The speaker explains the concept of text measures in Power BI and demonstrates how to create calculated columns and measures.

Creating Calculated Columns

Add a calculated column for profit by subtracting total cost from sales.

Mark the date table as the official date table.

Creating Measures Table

Create a blank table named "Measure" to organize measures.

Creating Sales Measures

Create a measure for total sales using the SUM function on the sales column in the sales data table.

Create a measure for sales previous year using time intelligence function SAMEPERIODLASTYEAR on the sales measure, passing in the date column from the date table.

Create a measure for sales versus previous year by subtracting sales previous year from current year's sales measure.

Create a measure for sales versus previous year in percentage by dividing (sales - sales previous year) by current year's sales measure.

Testing Measures with Visuals

The speaker tests newly created measures using visuals in Power BI report view.

Adding Visuals

Add a table visual to test measures.

Formatting Visuals

Change the format of the "Sales versus Sales Previous Year" measure to percentage.

Observations

The sales for each year and the corresponding previous year's sales are displayed correctly.

The measures for sales, sales previous year, and their comparison in percentage are accurate.

Conclusion

The transcript covers the process of creating slices to sort data and creating text measures in Power BI. It provides step-by-step instructions on how to create calculated columns and measures, as well as how to test them using visuals.

Calculating Profit and Time Intelligence Function

In this section, the speaker discusses calculating profit using time intelligence functions and compares it to the previous year's profit.

Last year, the focus was on calculating profit only for the previous year using time intelligence functions.

The measure "Profit versus Last Year" is calculated as the difference between current year's profit and last year's profit.

The measure "Profit versus Last Year Percentage" is calculated as the ratio of profit versus last year divided by profit.

The measure "Profit Margin" is calculated by dividing profit by sales.

It is recommended to use division function index instead of divide function to avoid errors when dividing by zero.

Total Cost Measure

This section covers the calculation of the total cost measure.

The total cost measure is calculated as a sum of some CH Theta total cost.

Visualization Part - Sales Comparison

In this section, the speaker focuses on creating visualizations for sales comparison.

The first visualization created is a combo chart that shows sales per product compared to last year's sales per product.

Product names are added to the x-axis and sales details are added to the y-axis.

A line graph representing previous year's sales is also added to the visualization.

Formatting options such as style, background, border, color, and title are adjusted for better visualization.

Conditional formatting is applied to highlight differences in sales. Green color indicates higher sales than previous year, while red color indicates lower sales.

Second Visualization - Sales Comparison by Month

This section focuses on creating a second visualization for sales comparison by month.

The second visualization is a copy of the first one, with adjustments made for displaying sales data by month instead of product.

The x-axis is changed to display month names and year.

Formatting options are adjusted accordingly for better visualization.

Conditional Formatting

This section covers the application of conditional formatting to highlight differences in sales.

Conditional formatting rules are applied based on whether current year's sales are greater or less than previous year's sales.

Sales greater than previous year are displayed in green, while sales less than previous year are displayed in red.

The colors can be customized as per preference.

Creating Sales Visualization in Power BI

In this section, the speaker demonstrates how to create a sales visualization in Power BI by comparing customer sales with last year's sales. The speaker uses a donut chart and applies filters to show the top five cities by sales.

Creating Sales by Top Five Cities Donut Chart

Add a donut chart visual in Power BI.

Set the data values to "sales" and the legend to "cities".

Apply a filter to show only the top five cities by sales.

Copy the formatting from one visual and apply it to another.

Format the labels and remove legends as needed.

Comparing Profit by Channel with Previous Year

In this section, the speaker demonstrates how to compare profit by channel with previous year's profit using an area chart in Power BI. The speaker adds channels on the x-axis and profit on both y-axes.

Creating Area Chart for Profit Comparison

Add an area chart visual in Power BI.

Set channels as x-axis and profit (current year) and profit (last year) as y-axis values.

Format axes, titles, colors, and legends as desired.

Visualizing Sales by Customer Type

In this section, the speaker shows how to create a clustered bar chart in Power BI to visualize sales by customer type. The speaker adds sales (current year) and sales (previous year) on the x-axis and customer names on the y-axis.

Creating Clustered Bar Chart for Sales Comparison

Add a clustered bar chart visual in Power BI.

Set sales (current year) and sales (previous year) on the x-axis.

Set customer names on the y-axis.

Format labels, legends, colors, and other elements as desired.

Creating Cards for Sales, Profit, Profit Margin, and Product Sold

In this section, the speaker demonstrates how to create cards in Power BI to display sales, profit, profit margin, and product sold. The speaker formats the cards and adjusts decimal values.

Creating Cards for Key Metrics

Add card visuals in Power BI.

Set data values for sales, profit, profit margin, and product sold.

Format the visuals by copying styles from other visuals.

Adjust decimal values and customize label colors.

The transcript is already in English.

Creating and Formatting the Card Visual

In this section, the speaker demonstrates how to create and format a card visual in Power BI.

Creating the Card Visual

To create a card visual, reduce its size to 35.

Confirm that the size looks fine.

Adding an Image to the Card Visual

Create an image and add it to the visual.

Browse and select an image to open.

Adjust the size of the image using "Image Fit" and choose "Fill" for proper sizing.

Move the image to the left and increase its size if needed.

Adding an Accent Bar

Create an accent bar on the left side of the card visual.

Choose green as the color for the accent bar.

Increase its size, adjust its shape to a rounded edge rectangle, and set corner radius to 9% or 10%.

Modifying Existing Card Visuals

Change images on individual card visuals by selecting each card separately.

Remove existing images and browse for new ones.

Repeat this process for different metrics such as profit margin or product server.

Creating a Comparison Card Visual

In this section, the speaker explains how to create a comparison card visual in Power BI.

Creating a New Comparison Card Visual

Show previous year's comparison by creating a new card version.

Add data for sales versus previous year or show only percentage change.

Formatting the Comparison Card Visual

Format values by changing their color, such as white for better visibility.

Add titles like "Versus Previous Year" and format them accordingly.

Customizing Callout Value

Reduce callout value font size to 15 for better fitment.

Remove background and visual border from callout value.

Applying Conditional Formatting

Format callout value color based on rules.

Use green for values greater than 0 and less than the maximum.

Use red for values greater than the minimum and less than 0.

Rearranging Visuals and Creating Duplicate Pages

In this section, the speaker demonstrates how to rearrange visuals and create duplicate pages in Power BI.

Rearranging Visuals

Arrange all visuals on the page as desired.

Renaming and Deleting Pages

Change the page name to "Sales Dashboard" or any other relevant name.

Create duplicate pages by right-clicking and selecting "Duplicate Page".

Rename duplicate pages accordingly.

Delete unwanted pages from the report.

Creating Tooltips for Enhanced Interactivity

In this section, the speaker explains how to create tooltips for enhanced interactivity in Power BI.

Creating a Tooltip Page

Create a new page specifically for tooltips.

Change canvas settings to type "Tooltip" and customize its background color.

Hide the tooltip page so it is not visible when publishing the report.

Adding Visuals to Tooltip Page

Create visuals on the tooltip page similar to other pages but with specific information for tooltips.

Enhancing Hover Interaction

Customize tooltips to display additional information when hovering over specific elements, such as showing profit margin or top five cities.

Creating and Formatting Visuals in Power BI

In this section, the speaker demonstrates how to create and format visuals in Power BI.

Copying and Pasting Visuals

The speaker copies a visual from one page and pastes it onto another page using the tooltip feature.

Unnecessary data, such as sales and product sold, is removed from the copied visual.

Formatting Visuals

The size of the visual is adjusted to 320.

Images are removed, and the size of callout values and labels are reduced.

The label size is set to 10, and the visual is moved to the top.

Creating Tooltip Visuals

Another visual is copied from the sales dashboard and pasted into the tooltip section.

The height is set to 200, width to 320, and formatting adjustments are made for data labels and title size.

Linking Tooltip Page

In the format pin properties of the visual, tooltip settings are selected to link it with a report page tooltip.

Publishing Dashboard

The completed dashboard is published into Power BI workspace by selecting publish button.

It prompts to save changes before publishing to Power BI service.

Accessing Published Report in Power BI Service

This section explains how to access a published report in Power BI service.

Navigating to Power BI Service

After publishing, access app.powerbi.com website.

Selecting Workspace

Choose the appropriate workspace (e.g., Data Wells demo) where the report is published.

Viewing the Report

The report and dataset are visible in the selected workspace.

The tooltip page is hidden, but other pages can be viewed.

Interactions with visuals, such as changing slices or hovering over elements to see tooltips, are demonstrated.

Exporting the Report

The report can be exported in PDF format for further analysis or sharing purposes.

Analyzing Insights from the Dashboard

This section focuses on analyzing insights from the dashboard.

Zooming In

The speaker zooms in on specific visuals to analyze insights more closely.

Identifying Insights for 2019

Insights for the year 2019 are discussed, including a decrease in CL by 10% and variations in sales performance across different months.

Certain customers show lower sales compared to the previous year, suggesting a need to address their concerns.

Maximizing Profit Margins

Export items have higher profit margins compared to wholesale channels, indicating a potential strategy shift towards export focus.

Leveraging Top Cities

The top five contributing cities are identified, suggesting targeted advertising efforts in these areas.

Conclusion

The transcript covers creating and formatting visuals in Power BI, accessing published reports in Power BI service, and analyzing insights from a dashboard. It provides step-by-step instructions and highlights key points related to visual creation, formatting techniques, linking tooltips, accessing reports online, exporting reports, and identifying actionable insights from data visualization.