Power BI Sales Project

Table of contents

1. Introduction
2. Step 1. Import Data
3. Step 2. Power Query
4. Step 3. Import a Dates table.
5. Step 4. Set up our Database Relationships
6. Step 5. Setting up our Measures
7. Step 6. Creating our Visualizations

Power BI Sales Project - Using Randomly Generated Product Names

This Project is meant as a tutorial/walkthrough for people looking to analyze Sales Data. In the folder, we will have the project already made, but I will loosely explain how to recreate it from scratch.

Step 1. Import Data

Select the sheets from the Excel xlsx file that have our data. Make sure to grab the Sales tabs for 2018, 2019, and 2020. Click "Transform Data here" We can rename the data sheets from "Product Data" to simply "Products" and follow this format for all of our tabs.

Step 2. Power Query

In Power Query we're going to Append the different Sales tabs (2018, 2019, and 2020) together into one database.

Click "Append as New" and drop the 3 tables together. Let's call it "Sales".

Uncheck Enable Load for the individual tables. This will make it much more clear for us in Step 4 when we setup our relationships.

Step 3. Import a Dates table.

One thing that's a great resource to have with Power BI is your own Dates table. My system is partly in Japanese so I'll share the Query you can import in the file. We use this Dates Table to apply filters, create slicers and timelines in our project. Set the Date Range from January 1st, 2018 to December 31s, 2020. You simply hit New>Blank Query> Paste the query in and fill out the date range, and it will generate this.

Step 4. Set up our Database Relationships

From here we will have to establish a relationship from the Dates table to the Sales table. Set it up as a 1 -> Many for Dates[Date] -> Sales[Purchase Date] by dragging the Date fields within Dates right onto the Purchase Date field within Sales.

Step 5. Setting up our Measures

Measures in Power BI are ways to do advanced calculations in a simple way, where it will adjust automatically with how we filter the data. You won't need every single measure in here, but I wanted to run through a lot of nice ones I've found, and some that are considered pretty fundamental for a Sales model. To make a new Measure, just hit the New Measure button from the Home tab.

Total Costs = SUMX( Sales, Sales[Quantity] * RELATED( Products[Cost]))


Average Quantity = AVERAGE(Sales[Quantity])


Cumulative Sales to Date = 
CALCULATE( [Total Sales],
     FILTER(ALLSELECTED( Dates ), Dates[Date] <= MAX( Dates[Date])))


Profit Margin = DIVIDE([Total Profits], [Total Sales], 0 )


Quantity Sold = SUMX(Sales, Sales[Quantity])


Sales LastYear = CALCULATE( SUMX(Sales, Sales[Quantity] * Sales[Price]), SAMEPERIODLASTYEAR( Dates[Date] ) )


Total Profits = [Total Sales] - [Total Costs]


Total Sales = SUMX(Sales, Sales[Quantity] * Sales[Price])


Total Transactions = COUNTROWS(Sales)


Weekend Profits! = CALCULATE( [Total Profits], 
     FILTER(Dates, Dates[isWeekend] ="Yes"))
#In order for this to work, add a custom column called isWeekend, or alternatively change this to 
Weekend Profits v2 = CALCULATE( [Total Profits], 
     FILTER(Dates, OR(Dates[Day In Week] = 5, Dates[Day In Week] = 6)))
     

Weekend Percentage = DIVIDE([Weekend Profits],[Total Profits], 0)

Step 6. Creating our visualizations.

Here's an example of a Visualization you can make. Add a Stacked Bar Chart. Drag the Customer Name and the [Total Values] Measure onto the graph.

You can click to the formatting tab and add Data labels for clarity.

Let's try adding a Matrix Table with the Product Names, Quantity Sold, Profit Margin, and Total Transactions.