Building a Sales Dashboard in Power BI from Scratch

A first Power BI sales dashboard is usually built backwards: charts first, questions later, and no way to tell whether the numbers are right. This guide builds one in the right order, on a small but complete database, and computes the value every measure should return, so the finished report can be checked line by line.

The data is Chinook, a sample database for a digital music store with invoices, invoice lines, customers, sales support employees, tracks and genres. Each release publishes a SQL Server script and a SQLite file under the MIT licence (Chinook on GitHub). Loading the SQL Server script and connecting Power BI to it is the most realistic route; connecting Power BI to SQL Server walks through the connection.

Step 1: Decide the questions

A sales dashboard should answer a few questions well:

1. How much did we sell, and is it growing?
2. How many orders and customers produced it, and what is a typical order worth?
3. Which products, countries and sales reps drive it?

Everything on the page should serve one of these. Write them down before opening Power BI, and share them with the people who will use the report; agreeing on the questions first prevents most rework later.

Step 2: Shape a star schema in Power Query

Chinook is normalised for transactions: an invoice line points to a track, the track to an album and a genre, the invoice to a customer, the customer to a support rep. For reporting, reshape it into a star schema:

• Sales (fact): one row per invoice line, with invoice ID, date, customer ID, track ID, unit price and quantity. Merge Invoice into InvoiceLine in Power Query to bring in the date and customer.
• Customer (dimension): customer attributes, plus the support rep's name merged in from Employee.
• Track (dimension): track, album, artist and genre flattened into one table.
• Date (dimension): one row per day.

Microsoft's guidance requires the date table to have unique dates with no gaps and to span full years (Microsoft Learn: date tables). A DAX calculated table does it in one expression; mark it as the date table afterwards:

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

Relate Date, Customer and Track to Sales with one-to-many relationships, filtering in a single direction.

Step 3: Write the measures

Revenue = SUMX ( Sales, Sales[UnitPrice] * Sales[Quantity] )
Orders = DISTINCTCOUNT ( Sales[InvoiceId] )
Customers = DISTINCTCOUNT ( Sales[CustomerId] )
Avg Order Value = DIVIDE ( [Revenue], [Orders] )
Revenue PY = CALCULATE ( [Revenue], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
Revenue YoY % = DIVIDE ( [Revenue] - [Revenue PY], [Revenue PY] )

DISTINCTCOUNT counts distinct values in a column (DAX reference: DISTINCTCOUNT), so orders and customers are counted once however many lines they have. SAMEPERIODLASTYEAR shifts the dates in the current context back one year (DAX reference: SAMEPERIODLASTYEAR), which is why the date table matters.

Step 4: Know what the measures should return

The quickest way to trust a report is to calculate the same numbers independently. The code below runs the equivalent logic on the Chinook SQLite file from the same release. That file holds the same invoices and amounts as the SQL Server script, but the SQL Server script dates every invoice exactly 12 years later, from 2021 to 2025, so the code shifts the dates to match what Power BI will show:

import sqlite3
import urllib.request
import pandas as pd

url = "https://github.com/lerocha/chinook-database/releases/download/v1.4.5/Chinook_Sqlite.sqlite"
urllib.request.urlretrieve(url, "chinook.sqlite")
sales = pd.read_sql("""
    SELECT il.InvoiceId, date(i.InvoiceDate) AS Date, i.CustomerId, il.UnitPrice, il.Quantity,
           e.FirstName || ' ' || e.LastName AS Rep
    FROM InvoiceLine il
    JOIN Invoice i ON i.InvoiceId = il.InvoiceId
    JOIN Customer c ON c.CustomerId = i.CustomerId
    JOIN Employee e ON e.EmployeeId = c.SupportRepId""",
    sqlite3.connect("chinook.sqlite"), parse_dates=["Date"])
sales["Date"] = sales["Date"] + pd.DateOffset(years=12)        # match the SQL Server script's dates
sales["Revenue"] = sales["UnitPrice"] * sales["Quantity"]

def measures(rows):
    return pd.Series({"Revenue": round(rows["Revenue"].sum(), 2),
                      "Orders": rows["InvoiceId"].nunique(),
                      "Customers": rows["CustomerId"].nunique(),
                      "Avg Order Value": round(rows["Revenue"].sum() / rows["InvoiceId"].nunique(), 2)})

print(measures(sales).to_string())
by_year = sales.groupby(sales["Date"].dt.year).apply(measures)
by_year["Revenue YoY %"] = by_year["Revenue"].pct_change().map(lambda v: "" if pd.isna(v) else f"{v:.1%}")
print(by_year.to_string())

Revenue            2328.60
Orders              412.00
Customers            59.00
Avg Order Value       5.65
      Revenue  Orders  Customers  Avg Order Value Revenue YoY %
Date                                                           
2021   449.46    83.0       46.0             5.42              
2022   481.45    83.0       46.0             5.80          7.1%
2023   469.58    83.0       47.0             5.66         -2.5%
2024   477.53    83.0       47.0             5.75          1.7%
2025   450.58    80.0       46.0             5.63         -5.6%

If the Power BI cards show 2,328.60 revenue, 412 orders, 59 customers and an average order value of 5.65, the model and measures are right. Note that customers by year add up to far more than 59: the same customers buy every year. A Customers total must be a distinct count, never a sum of yearly counts.

Step 5: Compare sales reps fairly

by_rep = sales.groupby("Rep").apply(measures)
by_rep["Revenue per Customer"] = (by_rep["Revenue"] / by_rep["Customers"]).round(2)
print(by_rep.sort_values("Revenue", ascending=False).to_string())

               Revenue  Orders  Customers  Avg Order Value  Revenue per Customer
Rep                                                                             
Jane Peacock    833.04   146.0       21.0             5.71                 39.67
Margaret Park   775.40   140.0       20.0             5.54                 38.77
Steve Johnson   720.16   126.0       18.0             5.72                 40.01

Ranked by revenue, Jane Peacock leads. Ranked by revenue per customer, Steve Johnson does: he looks after fewer customers, who spend slightly more. A rep table should show both, or it rewards whoever was assigned the most accounts.

Step 6: Lay out the page

• Top row: cards for Revenue, Orders, Customers and Avg Order Value, each with the year-over-year change beneath.
• Middle: revenue by month as a line chart.
• Bottom: revenue by genre and by country as sorted bar charts, and the sales rep table.
• Slicers: year and country.

KPIs and cards in Power BI covers designing the top row.

Add interactivity with purpose

Clicking a genre bar filters every other visual on the page by default, which is usually what users want. Check each pair with Edit interactions, and turn off filtering where it confuses, for example on the cards if they should always show the slicer selection. A drillthrough page for a single customer, reached by right-clicking a customer in any visual, answers the natural follow-up question without cluttering the main page.

Check before sharing

1. Compare every card with the expected values in step 4, with no slicers applied.
2. Select one year and one country, and confirm a value by hand from the source.
3. Confirm that totals in tables equal the card values.
4. Open the report as a viewer would, at the resolution they use.

Be honest about sample data

Chinook is generated data, and it shows: monthly revenue is identical in most months, a pattern no real store produces. That makes it good for learning to build and check a model, but it should not be presented as a real business story. In a portfolio, say what the data is and put the emphasis on the model, the measures and the checks.

Step 7: Publish

Publish to a workspace in the Power BI service, set up scheduled refresh through a gateway if the data is in an on-premises SQL Server, and share with the right audience; publishing, sharing and workspaces covers the options.

More project ideas are in Power BI projects for your portfolio. A sales dashboard is one of the projects in the Power BI course.

Sources

• Chinook sample database (GitHub) — GitHub
• Design guidance for date tables in Power BI Desktop (Microsoft Learn) — Microsoft Learn
• SAMEPERIODLASTYEAR function (DAX reference, Microsoft Learn) — Microsoft Learn
• DISTINCTCOUNT function (DAX reference, Microsoft Learn) — Microsoft Learn