Skip to content
Tutorials Center
My learning

Topics

Lesson 48 of 56 ยท Projects

Project: Sales Performance Dashboard

Build a complete Power BI sales dashboard: star schema, date table, DAX measures, two report pages, and a pandas check of every number you show.

Time to put the course together. In this project you build a Sales Performance Dashboard from the first table to the last visual: a star-schema model, a date table, a small set of DAX measures, two report pages and a check that every number on the page is right. The skills come from earlier lessons, so each step links back to them.

Power BI Desktop is a graphical tool, so the steps are written as instructions you follow in the application, and the DAX is read-only. The data is small enough to type into the browser, and the runnable Python examples are stand-ins: they build the same three tables with pandas and recompute every measure and visual number independently. That is a real technique, not just a teaching device. When you build a model for work, an independent calculation of the headline numbers is the best defence against a wrong measure or a wrong relationship.

The brief

A small furniture company wants a dashboard that answers five questions:

  1. How much did we sell, and how profitable is it?
  2. How does this year compare with last year, at the same point in the year?
  3. Which regions and categories drive sales?
  4. Which products are the top sellers?
  5. How is the current year building up month by month?

The data covers 18 orders from January 2024 to June 2025, for four products and five customers in three regions. In a real project the source would be a database or a folder of files (see Connecting to Data Sources); here you create three small tables.

The plan

Model Sales fact plus Product, Customer and Date.
Measures Sales, profit, margin, YoY.
Pages Overview and Trends.
Check Every number verified.
1 / 2

Step 1: load and shape the data

Create three queries, for example from Excel sheets or CSV files:

  • Sales (the fact table): OrderID, OrderDate, CustomerID, ProductID, Quantity. One row per order, so the grain is "one order".
  • Product: ProductID, Product, Category, UnitPrice, UnitCost.
  • Customer: CustomerID, Customer, Region.

In the Power Query editor (see The Power Query Editor and Cleaning Data: Types, Nulls, Trim and Duplicates), set the data types: whole numbers for IDs and Quantity, Date for OrderDate, decimal numbers for prices. Trim text columns and check for duplicates on the key columns. A wrongly typed date column is the most common reason time intelligence later returns blanks.

Notice that the revenue is not stored in the fact table. Revenue is Quantity x UnitPrice, and the price lives in Product. You could compute it in Power Query, but a measure keeps the fact table lean, so you will do it in DAX.

Step 2: build the star schema

In Model view create these relationships (see Star Schema: Facts and Dimensions and Relationships, Cardinality and Filter Direction):

From (one side) To (many side) Direction
Product[ProductID] Sales[ProductID] Single
Customer[CustomerID] Sales[CustomerID] Single
Date[Date] Sales[OrderDate] Single

Each dimension has unique keys, each relationship is one-to-many and filters flow from the dimensions to the fact. Hide the key columns in the fact table, and hide UnitPrice and UnitCost from report view if you plan to use measures only (see Model View, Hierarchies, Formatting and Display Folders).

The first runnable stand-in builds the same three tables and runs the integrity checks you should run on any model: is the grain what you think, and does every fact row find its dimension row?

Python Stand-in: tables and integrity checks

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

There are no orphan keys, the order id is unique and the data runs from 15 January 2024 to 19 June 2025. That last fact matters: 2025 is only half a year, which will shape the year-over-year measure in step 4.

In Power BI, orphan keys show up as a blank row in visuals grouped by that dimension; the stand-in's isin check finds them before they surprise you.

Step 3: the date table

Create a calendar table, mark it as the date table and relate it to Sales[OrderDate] (see Date Tables and Time Intelligence Setup):

DAX
Date =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2024, 1, 1 ), DATE ( 2025, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Month Number", MONTH ( [Date] ),
    "Month", FORMAT ( [Date], "mmm" )
)

Then use Table tools > Mark as date table, and sort Month by Month Number so the months do not appear alphabetically. Use CALENDAR over whole years, so the table has no gaps, even though the data stops in June.

Step 4: the measures

Create a separate table for measures, for example with Enter data named _Measures, and add the measures below (see DAX Basics: Syntax, Columns and Measures, Aggregators and Iterators: SUM, SUMX, AVERAGEX and Time Intelligence: YTD, Prior Year and Moving Averages):

DAX
Total Sales =
SUMX ( Sales, Sales[Quantity] * RELATED ( Product[UnitPrice] ) )

Total Cost =
SUMX ( Sales, Sales[Quantity] * RELATED ( Product[UnitCost] ) )

Profit = [Total Sales] - [Total Cost]

Margin % = DIVIDE ( [Profit], [Total Sales] )

Orders = DISTINCTCOUNT ( Sales[OrderID] )

Avg Order Value = DIVIDE ( [Total Sales], [Orders] )

Sales YTD = TOTALYTD ( [Total Sales], 'Date'[Date] )

Sales PY YTD =
CALCULATE ( [Sales YTD], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )

YoY % = DIVIDE ( [Sales YTD] - [Sales PY YTD], [Sales PY YTD] )

SUMX with RELATED multiplies row by row, because each order needs its own product's price. DIVIDE protects against dividing by zero. TOTALYTD and SAMEPERIODLASTYEAR rely on the date table.

There is one design question to settle: what should YoY % show for a half-finished year? If a card shows the whole of 2025 against the whole of 2024, it compares six months with twelve. The YTD measures above compare January to the latest date with the same span a year earlier, which is the honest answer. The stand-in below recomputes the measures, and also shows the trap.

Python Stand-in: recompute every measure

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Keep these numbers: total sales 12,640, profit 5,595, margin 44.3%, 18 orders and an average order of 702.2. YTD to June is 3,540 for 2024 and 5,580 for 2025, so year-over-year growth is +57.6%. The naive comparison of full-year totals gives a decline of 21.0%, which is simply wrong, and you should expect a careless report to show exactly that. When you finish the model, put these numbers on a card and compare: if Power BI shows something different, the model, not the Python, is the first suspect.

Step 5: report page 1, Overview

Add a page called Overview (see Choosing the Right Visual and Formatting, Themes and Report Design):

  • A row of cards at the top: Total Sales, Profit, Margin %, Orders, and a card or KPI for YoY % (see Conditional Formatting, KPIs and Cards). Format Margin % and YoY % as percentages.
  • A bar chart: Customer[Region] on the axis and Total Sales as the value, sorted descending.
  • A bar chart of products: Product[Product] and Total Sales, sorted descending, optionally with a rank measure.
  • A donut or column chart of Product[Category].
  • Slicers for Date[Year] and Customer[Region] (see Filters, Slicers and Sync).

Give each visual a clear title that says what it shows ("Sales by region"), and put the most important information in the top left. Use one colour for the main series and keep the page uncluttered.

Add a page called Trends:

  • A clustered column chart: Date[Month] on the axis, Total Sales as the value and Date[Year] as the legend, to compare each month with the same month last year.
  • A line chart of cumulative Sales YTD by month for the current year.
  • A matrix with Region on rows, Year on columns and Total Sales as the value, with conditional formatting on the change (see Conditional Formatting, KPIs and Cards).
  • A drill-through page or tooltip page with order details (see Drill-through, Tooltips, Bookmarks and Buttons).

Remember that visuals that group by month for 2025 show only January to June, so the 2025 line stops there. That is correct, not a bug, and a note under the chart ("Data to 19 June 2025") helps readers understand it.

Step 7: verify every number

Everything you display should be independently checkable. The last stand-in recomputes the numbers behind the visuals: sales by region with percent of total, the product ranking, the first-half comparison by region and the 2025 running total.

Python Stand-in: numbers behind the visuals

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Compare each block with the matching visual:

  • South leads with 5,860 (46.4%), then North with 5,020 (39.7%) and East with 1,760 (13.9%). The three percentages add up to 100.
  • Chair (4,320) narrowly beats Desk (4,200), even though a desk costs far more per unit: volume matters.
  • In the first half of 2025 against the first half of 2024, South is up 146.2% and East up 162.5%, while North is down 19.8%. The headline growth of 57.6% hides a region that is shrinking, which is exactly the kind of insight a dashboard should surface.
  • The 2025 running total climbs from 1,200 in January to 5,580 in June, ending exactly on the YTD number from step 4.

That last equality is a good habit: two different visuals that should agree, do agree. If the running total's last point did not equal the YTD card, one of them would be wrong.

Step 8: polish and publish

Extension ideas

  • Add a Targets table with a monthly sales target per region and a KPI visual showing progress.
  • Add a ranking measure and a Top N filter for the best customers (see Ranking, Top N and Percent of Total).
  • Create a field parameter that lets users switch the chart between sales, profit and orders (see Common DAX Patterns).
  • Replace the typed tables with files from a folder and use Merge and Append Queries to combine a month of data per file.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why does the project compute revenue with SUMX and RELATED instead of summing a stored column?

    The fact table has quantity but not price. SUMX goes row by row, and RELATED looks up the price in the Product table for each row.
  2. 2. The data stops in June 2025. Why is comparing full-year totals for 2025 and 2024 misleading?

    Six months of 2025 against twelve months of 2024 gives a false decline. Year-to-date versus the same period last year compares like with like.
  3. 3. What does the pandas check in step 7 give you?

    Recomputing the headline numbers outside the model reveals wrong measures or relationships. If both methods agree, you can trust the page.
  4. 4. What is the grain of the Sales fact table in this project?

    Each row is one order, so order_id is unique. Knowing the grain tells you what a count of rows means and which dimensions can slice the data.

Summary

  • Build in order: load and clean, relate in a star schema, add a date table, write measures, then add visuals.
  • The fact table holds the order grain; Product, Customer and Date are dimensions with one-to-many relationships.
  • Use SUMX with RELATED when a calculation needs values from several tables, DIVIDE for safe ratios and TOTALYTD/SAMEPERIODLASTYEAR for like-for-like comparison.
  • A half-finished year must be compared with the same period last year: here +57.6%, not the misleading -21.0%.
  • Headline numbers can hide detail: total growth was positive while North fell 19.8%.
  • Verify every number independently; the Python stand-ins here are a check, not the real engine.
  • Finish with design, accessibility, publishing, refresh and security.

Next, practise the same skills on a finance report in Project: Profit and Loss Report.