Final Project: Build a Complete Sales Dashboard in Power BI | Free Power BI Course: From Beginner to Advanced

What you'll learn

Build a professional sales dashboard from start to finish.
Combine Power Query, a star schema, DAX and interactivity in one project.
Validate the numbers and publish securely.
Package the project for your portfolio and plan your next steps.
Answer the five business questions of Summit's dashboard with verified numbers.

📥 Project data

It has the structure described in Step 1, with sales for 2025 and 2026.

summit-sales-power-bi.xlsx · use this file if you'd rather not generate your own data in Excel

Time to put it all together. In this project you'll build Summit Supply's sales dashboard the way you would on the job: from raw data to a published report with advanced measures, navigation and security. When you're done you'll have a portfolio piece and a template for any sales report.

$247,709.872025 sales: your first validation should match this number exactly

$268,259.162026 sales, up 8.3% from 2025

97.4%of the 2026 target ($275,550): the gap your dashboard needs to explain

Data
Power Query
Model
Measures
Pages
Security
Final check

Step 1: the data

The file summit-sales-power-bi.xlsx already includes the five tables. If you'd rather generate your own data, create an Excel workbook with five sheets, each formatted as a table:

Table	Columns	Rows
Products	ProductID, Product, Category, Brand, UnitCost, ListPrice	12 (P01 to P12, in 3 categories)
Customers	CustomerID, Customer, City, State, Region, Channel (Convenience Store, Independent Grocer, Wholesale)	40
SalesReps	RepID, SalesRep, Region, Email	4
Sales	OrderID, OrderDate, CustomerID, ProductID, RepID, Quantity, UnitPrice	4,430 in the file (2,205 orders); 2,000+ if you generate your own
Targets	RepID, Year, Jan, Feb, … Dec (one row per rep and year)	8

To generate 2,000 sample sales of your own, use these formulas on the Sales sheet (from row 2) and then copy and paste them as values so they stop changing:

OrderDate:   =DATE(2025,1,1)+RANDBETWEEN(0,729)
CustomerID:  ="C"&TEXT(RANDBETWEEN(1,40),"00")
ProductID:   ="P"&TEXT(RANDBETWEEN(1,12),"00")
Quantity:    =RANDBETWEEN(1,48)
UnitPrice:   =XLOOKUP(D2,Products[ProductID],Products[ListPrice])*(1-RANDBETWEEN(0,10)/100)

For RepID, look it up from the customer's region. If your Excel doesn't have XLOOKUP, use VLOOKUP. Need a refresher? See Excel and Google Sheets for data analysis in our Data Analytics course.

Step 2: Power Query (Lesson 2)

Check data types: dates as Date, amounts as Decimal Number, codes as Text.
Trim and standardize City and Region text.
In Sales, add an Amount column = Quantity × UnitPrice (or calculate it with SUMX in a measure).
In Targets, unpivot the months and add a date column (first day of the month) to relate it to the calendar.

Step 3: the model (Lessons 5 and 8)

Sales and Targets (facts) 4,430 · 96Targets has 96 rows after unpivoting.
SalesReps and Calendar sharedThey filter both fact tables, so you can compare sales and target by rep and month.
Products and Customers Sales onlyThat's why targets can't be viewed by product or customer.

Every relationship is one-to-many, with Single direction from the dimension to the facts.

Create the Calendar table with DAX, mark it as a date table and sort the month by its number.
One-to-many relationships with Single direction. Targets relates to Calendar and SalesReps.
Hide the keys and turn off Auto date/time.

Step 4: the measures

Create a "_Measures" table and organize the measures in display folders (in each measure's Properties pane):

Folder	Measures	Lesson
Base	Total Sales, Total Cost, Gross Profit, Gross Margin %, Units, Orders, Customers, Avg Order Value	6
Targets	Target, Target Attainment % (Sales ÷ Target), Variance to Target	6–7
Time	Sales YTD, Sales PY, YoY %, YTD YoY %, 3M Moving Avg	8
Share	% of Total Products, % Sales Top 5	7, 11
Customers	New Customers, Returning Customers, Lost Customers, Customer Rank	11
Formatting	Dynamic Title, Attainment Color	9

Target = SUM ( Targets[Target] )

Target Attainment % = DIVIDE ( [Total Sales], [Target] )

Variance to Target = [Total Sales] - [Target]

Validate before you design

Before building a single chart, compare against Excel: total 2025 sales, one specific month and one customer. If any number is off, review Power Query and the relationships. Designing on top of wrong numbers is wasted time.

With the course file, these are your control figures. Calculate them in Excel too (with SUMPRODUCT or a pivot table) and compare:

Check	Measure in Power BI	Expected result
2025 sales	Total Sales with Year = 2025	$247,709.87
January 2025 sales	Total Sales with Year-Month = 2025-01	$21,989.03
One customer: Family Convenience 38	Total Sales and Orders, no date filter	$16,579.68 across 68 orders
2026 targets	Target with Year = 2026	$275,550

1. Targets are by rep and month. What happens if you put the Target measure in a matrix by Product?

The target is split proportionally across products.
Every product shows the same total target, because Targets isn't related to Products.
You get an error.

The Product filter never reaches the Targets table, so every row shows the total. You can only analyze targets by the dimensions both tables share (rep and date).

Step 5: the pages (Lessons 3, 4 and 9)

Summary

Cards: Sales, Target Attainment %, Gross Margin %, YTD YoY %, Customers. A combo chart of sales vs. prior year by month. Attainment bars by rep colored with Attainment Color.

Slicers: Year, Region and Channel.

Products

Category → Product hierarchy with rank, % of total and gross margin %. A scatter chart of sales vs. margin % by product.

Extra: a tooltip with the monthly trend.

Customers

New, returning and lost customers by month. Sales by segment A/B/C.

Action: a table of lost customers with their rep.

Hidden pages

Rep detail: drillthrough by SalesRep with attainment by month, customers and products.

Product tooltip: 12-month trend and margin.

Add a page navigator, a theme in company colors, dynamic titles and a mobile layout for the Summary page.

Step 6: security and publishing (Lesson 10)

Create a dynamic role: filter SalesReps with [Email] = USERPRINCIPALNAME(), plus a "Leadership" role with no filter.
Test it with View as (you can simulate another user by typing their email).
Publish to a workspace, set up refresh and create an app for the sales team.

Step 7: the final checklist

The numbers tie out to the source in at least three checks (use the validation table in Step 4).
Every measure has a format and a description.
Each page answers specific questions with 5 to 7 visuals.
Drillthrough, tooltips, bookmarks and synced slicers all work.
Performance analyzer shows no visual taking more than a couple of seconds.
Security shows each rep only their own data (Emily Carter: $133,438.18 in 2025–2026).
A real user understands the report without you explaining it.

2. Your 2025 sales card shows $247,201.86, but the validation says $247,709.87. The difference is exactly $508.01. What's the most likely cause?

A Power BI rounding error.
The 2025 targets are missing.
The 3 sales with code P99 were dropped, for example by a "Product is not blank" filter or an inner join in Power Query.

$508.01 is exactly what the P99 sales add up to (Lesson 5), and all three are from 2025. Knowing your control figures lets you diagnose problems in seconds.

3. What should you do before you start designing the project's pages?

Validate that the model's totals tie out to the source data.
Choose the theme colors.
Publish the report.

If the base numbers are wrong, everything built on top of them is wrong too. Validating first saves rework.

Copilot and AI in Power BI

Microsoft keeps adding AI features to Power BI, such as Copilot to suggest report pages, write DAX measures or answer questions about your data, and visuals that explain changes. Availability depends on your license and region and changes often. Treat them as assistants: what you learned in this course is what lets you tell whether a suggested measure is right.

Your portfolio and next steps

Portfolio: publish screenshots and a short video of the report, and explain the business problem, the model and 3 key measures. Use fictional or public data, never a real company's data. The portfolio project lesson of our Data Analytics course shows how to write it up.
Certification: Microsoft's Power BI Data Analyst certification (exam PL-300) is valued by employers. Lesson 16 covers it in detail.
Go deeper: the M language in Power Query, advanced DAX patterns, models with several fact tables and Microsoft Fabric for larger data solutions.
Python: for analysis beyond Power BI, see Python for data analysis with pandas.

4. Copilot suggests a DAX measure for year-over-year growth. What do you do?

Use it as is: AI doesn't make DAX mistakes.
Review it and check the result against a month whose value you know before using it.
Always throw it away.

AI speeds you up, but you validate: filter context and date tables are classic sources of subtle errors.

Final project: Summit Supply's sales dashboard

Complete the 7 steps in this lesson. To call it done, your report needs:

A star schema with a calendar and unpivoted targets.
At least 20 measures, formatted and organized in folders.
Three visible pages, one drillthrough page and a custom tooltip.
A tested dynamic security role.
Three documented number checks.

See solution

Your dashboard should answer these five business questions in two or three clicks:

Will we hit this year's target? Which rep is furthest behind, and since when?
Which category is growing fastest vs. last year? Which has the weakest margin?
What share of sales depends on the top 5 products?
How many new customers do we win each month, and how many do we lose?
Which channel (convenience store, independent grocer, wholesale) has the highest average order value?

Compare your answers with the course file:

2026 target: missed; 97.4% (short by $7,290.84). Furthest behind is Emily Carter at 96.0% (short by $3,133.24), and her year-to-date attainment has been below target since January (93.1%). Rachel Kim started above target (104.8% in January) and has been below since February.
Categories: pantry grows fastest (+16.2%) and also has the weakest margin (27.6%).
Concentration: the top 5 products make 66.9% of sales.
Customers: 37 new in January 2025, 2 in February, 1 in March and none after; the focus is keeping the ones you have.
Average order value by channel: convenience stores $238.20; independent grocers $232.12; wholesale $231.43.

2026 sales growth vs. 2025, by category

Pantry+16.2%

Dairy+11.2%

Beverages−2.2%

Look at the full story: wholesale sells the most in total, but convenience stores place the biggest orders; pantry drives growth, but at the lowest margin; and beverages, the best-margin category (Lesson 6), is the only one shrinking. Those three sentences are worth more in your portfolio than any chart.

Common final project mistakes

Starting with design and discovering at the end that the total doesn't tie out.

Validate the four control figures before the first chart.

Loading Targets with months as columns and creating 12 measures, one per month.

Unpivot and create a single Target measure.

40 loose measures with no format or folders.

A _Measures table, display folders, formats and a description for each measure.

Showing the project in your portfolio with pretty screenshots only.

Business problem, model, 3 key measures and 3 findings with the decision they support.

Publishing a .pbix with your employer's real data "to show experience".

Fictional data like Summit's or public data; never confidential information.

Summary

Power BI follows a workflow: get, transform, model, calculate, visualize and publish.
Power Query cleans once and for all; the star schema makes everything work.
DAX measures, CALCULATE and time intelligence turn data into answers.
Good design and interactivity get the report used; security and publishing get it to the right people.
Always validate the numbers: trust in the data is a report's most valuable asset.

You've completed the Advanced level and your final project! Next comes Level 4: Expert, with the reports and techniques most in demand in finance and leadership.

Next lesson: Income Statement (P&L) in Power BI with DAX. You'll build a complete income statement with a layout table and a single DAX measure.