Build Your First Power BI Report: Step-by-Step with a Real Dataset

2026/04/2711 min read
bookmark this
Build Your First Power BI Report: Step-by-Step with a Real Dataset
Photo by Karl Pawlowicz on Unsplash

Table of Contents

Introduction

This guide walks through building a complete, interactive Power BI report from a single CSV file. The dataset is a daily retail sales log spanning January 2023 to September 2025 — covering five product categories, three customer segments, pricing, discounts, marketing spend, and units sold.

By the end you will have a two-page report with KPI cards, trend lines, category breakdowns, and a marketing ROI view — all driven by interactive slicers.

Prerequisites: Power BI Desktop installed (free download at powerbi.microsoft.com). No account required for Desktop.

The Dataset

File: retail-sales-2023-2025.csv
Rows: ~1,000 daily records
Date range: 01-Jan-2023 to 26-Sep-2025

Column Type Description
Date Text (DD-MM-YYYY) Transaction date
Product_Category Text Electronics, Fashion, Home Decor, Sports, Toys
Price Decimal Unit price
Discount Decimal Discount amount applied
Customer_Segment Text Occasional, Premium, Regular
Marketing_Spend Decimal Daily marketing budget for that row
Units_Sold Integer Number of units sold

Sample Data of the CSV

Date,Product_Category,Price,Discount,Customer_Segment,Marketing_Spend,Units_Sold
01-01-2023,Sports,932.8,35.82,Occasional,6780.38,32
02-01-2023,Toys,569.48,3.6,Premium,6807.56,16
03-01-2023,Home Decor,699.68,3.56,Premium,3793.91,27
04-01-2023,Toys,923.27,0.61,Premium,9422.75,29
05-01-2023,Toys,710.17,47.83,Premium,1756.83,17
...

Step 1 — Load the CSV into Power BI Desktop

  1. Open Power BI Desktop.
  2. On the Home ribbon, click Get Data → Text/CSV.
  3. Browse to retail-sales-2023-2025.csv and click Open.
  4. Power BI previews the data. Verify the column headers look correct.
  5. Click Transform Data — this opens the Power Query Editor where you will clean the data before loading it.

Step 2 — Clean and Transform Data in Power Query

The Date column arrives as plain text in DD-MM-YYYY format. Power BI will not recognise it as a date automatically, so you need to parse it manually.

2a — Parse the Date column

  1. Select the Date column.
  2. On the Transform ribbon, click Data Type → Text (keep it as text for now).
  3. On the Add Column ribbon, click Custom Column.
  4. Name it Date_Parsed and enter the formula:
= Date.FromText([Date], [Format="dd-MM-yyyy"])
  1. Click OK. A new column appears with proper date values.
  2. Right-click the original Date column → Remove.
  3. Rename Date_Parsed to Date by double-clicking the column header.
  4. On the Transform ribbon, confirm the data type is set to Date.

2b — Set remaining column types

Select each column and assign the correct type from the Transform → Data Type menu:

Column Type
Product_Category Text
Price Decimal Number
Discount Decimal Number
Customer_Segment Text
Marketing_Spend Decimal Number
Units_Sold Whole Number

2c — Rename the query

In the Queries panel on the left, double-click the query name and rename it to Sales.

2d — Close and Apply

Click Close & Apply on the Home ribbon. Power BI loads the data into the model.

Step 3 — Create a Date Table

A dedicated date table unlocks time intelligence DAX functions like TOTALYTD and DATEADD. Create one with DAX.

  1. In Power BI Desktop, go to the Modeling ribbon → New Table.
  2. Paste the following DAX expression:
Date Table =
ADDCOLUMNS(
    CALENDAR(DATE(2023,1,1), DATE(2025,12,31)),
    "Year",           YEAR([Date]),
    "Month Number",   MONTH([Date]),
    "Month Name",     FORMAT([Date], "MMM"),
    "Month Year",     FORMAT([Date], "MMM YYYY"),
    "Quarter",        "Q" & QUARTER([Date]),
    "Year Quarter",   YEAR([Date]) & " Q" & QUARTER([Date]),
    "Week Number",    WEEKNUM([Date])
)
  1. Click the checkmark to confirm.
  2. Mark the table as a date table: right-click Date Table in the Fields pane → Mark as date table → select the Date column.
  3. Create a relationship: go to Model view (icon on the left sidebar), then drag Date Table[Date] onto Sales[Date]. A one-to-many relationship is created automatically.

Step 4 — Build DAX Measures

Switch to Report view and create a dedicated measures table for organisation.

Create a measures table: Go to Modeling → New Table, type _Measures = {}, and press Enter. This creates an empty placeholder table.

Now create each measure by clicking Modeling → New Measure while the _Measures table is selected.

Core metrics

-- Total gross revenue (Price × Units Sold)
Gross Revenue =
SUMX(Sales, Sales[Price] * Sales[Units_Sold])

-- Net revenue after discounts
Net Revenue =
SUMX(Sales, (Sales[Price] - Sales[Discount]) * Sales[Units_Sold])

-- Total units sold
Total Units =
SUM(Sales[Units_Sold])

-- Total marketing spend
Total Marketing Spend =
SUM(Sales[Marketing_Spend])

-- Average discount per transaction
Avg Discount =
AVERAGE(Sales[Discount])

Marketing ROI

Marketing ROI measures how much net revenue was generated per dollar of marketing spend.

Marketing ROI =
DIVIDE([Net Revenue], [Total Marketing Spend], 0)

Year-over-year comparison

Net Revenue PY =
CALCULATE([Net Revenue], SAMEPERIODLASTYEAR('Date Table'[Date]))

Net Revenue YoY % =
DIVIDE([Net Revenue] - [Net Revenue PY], [Net Revenue PY], BLANK())

Monthly trend helper

Net Revenue MTD =
TOTALMTD([Net Revenue], 'Date Table'[Date])

Step 5 — Build the Report (Page 1: Sales Overview)

Right-click the page tab at the bottom and rename it Sales Overview.

Visual 1 — KPI Cards (top row)

Add four Card visuals across the top of the canvas:

  1. Drag [Gross Revenue] onto the canvas → change visual type to Card.
  2. Repeat for [Net Revenue], [Total Units], and [Total Marketing Spend].
  3. In the Format pane, enable Callout value and set font size to 28. Disable category labels or rename them for clarity.

Tip: Hold Ctrl and click all four cards, then use Format → Align → Top to align them in a row.

Visual 2 — Monthly Net Revenue Trend (line chart)

  1. Insert a Line chart.
  2. Set X-axis to Date Table[Month Year].
  3. Set Y-axis to [Net Revenue].
  4. Add [Net Revenue PY] as a second line (drag it to the Y-axis field well).
  5. In Format → Lines, change the Net Revenue PY line to a dashed style in a lighter colour.

This gives readers an instant year-over-year comparison on the same chart.

Visual 3 — Net Revenue by Product Category (bar chart)

  1. Insert a Clustered bar chart.
  2. Set Y-axis to Product_Category.
  3. Set X-axis to [Net Revenue].
  4. Sort the chart by Net Revenue descending: click the ellipsis (…) on the visual → Sort by → Net Revenue → Descending.

Visual 4 — Units Sold by Customer Segment (donut chart)

  1. Insert a Donut chart.
  2. Set Legend to Customer_Segment.
  3. Set Values to [Total Units].
  4. In the Format pane, set the Inner radius to 50% and enable data labels showing Percent of total.

Step 6 — Build the Report (Page 2: Marketing Efficiency)

Right-click the page tab and rename it Marketing Efficiency.

Visual 1 — Marketing ROI by Category (column chart)

  1. Insert a Clustered column chart.
  2. Set X-axis to Product_Category.
  3. Set Y-axis to [Marketing ROI].
  4. Add a Constant line at 1.0 (Home → Analytics pane → Constant Line) to visually mark the break-even point.

Visual 2 — Scatter: Marketing Spend vs Net Revenue

A scatter chart reveals whether higher spending actually drives revenue.

  1. Insert a Scatter chart.
  2. Set X-axis to [Total Marketing Spend].
  3. Set Y-axis to [Net Revenue].
  4. Set Legend to Product_Category.
  5. Set Size to [Total Units] — bubble size encodes volume.

This allows you to spot which categories get the most value from marketing dollars.

Visual 3 — Monthly Marketing Spend vs Net Revenue (line and clustered column)

  1. Insert a Line and clustered column chart.
  2. Set X-axis to Date Table[Month Year].
  3. Set Column Y-axis to [Total Marketing Spend].
  4. Set Line Y-axis to [Net Revenue].

Visual 4 — Marketing ROI over time (line chart)

  1. Insert a Line chart.
  2. Set X-axis to Date Table[Month Year].
  3. Set Y-axis to [Marketing ROI].
  4. Add a trend line: Analytics pane → Trend line → Add.

Step 7 — Add Slicers for Interactivity

Slicers make both pages interactive. Add them to a consistent position — typically a narrow left sidebar or a top strip.

Year slicer

  1. Insert a Slicer.
  2. Set Field to Date Table[Year].
  3. In Format → Slicer settings → Style, choose Dropdown or Tile depending on your layout preference.

Product Category slicer

  1. Insert another Slicer.
  2. Set Field to Sales[Product_Category].
  3. In Format → Slicer settings, enable Multi-select so readers can pick multiple categories.

Customer Segment slicer

  1. Insert a Slicer for Sales[Customer_Segment].

Sync slicers across pages

To have the same slicer control both pages:

  1. Select a slicer.
  2. Go to View → Sync slicers.
  3. In the panel that appears, check the box under Sync for both pages.
  4. Repeat for each slicer.

Step 8 — Format and Polish

A clean, consistent layout makes the report easier to read.

Page background and theme

  1. Go to View → Themes and choose a built-in theme such as Executive or Frontier, or import a custom JSON theme.
  2. On each page, go to Format → Page background and set a subtle colour (e.g., a very light grey #F5F5F5) to give cards visual separation from the canvas.

Visual borders and shadows

  1. Select all visuals (Ctrl+A).
  2. In Format → General → Effects, enable Shadow with low transparency and a soft blur for a subtle depth effect.

Titles

Every visual should have a clear, descriptive title:

  • Format → General → Title → Text — type a human-readable label such as "Net Revenue by Category (2023–2025)".
  • Keep font size consistent (e.g., 12pt for visual titles, 10pt for axis labels).

Cross-highlighting

By default, clicking a bar in one chart highlights related values in others. This is on by default but can be toggled per visual under Format → Edit interactions on the Format ribbon.

Step 9 — Publish to Power BI Service

Once you are happy with the report in Desktop:

  1. Click Publish on the Home ribbon.
  2. Sign in with your work or school Microsoft account if prompted.
  3. Choose a Workspace to publish into (your personal "My workspace" works fine for testing).
  4. After publishing, click the link to open the report in app.powerbi.com.
  5. In the Service, click Pin to dashboard on any visual to build a summary dashboard.
  6. To keep the data fresh, go to the dataset settings in the Service and configure Scheduled Refresh (requires a Power BI Gateway for local files).

Summary

Here is what you built:

Page Visuals
Sales Overview 4× KPI cards, monthly trend (YoY), bar chart by category, donut by segment
Marketing Efficiency ROI by category, scatter (spend vs revenue), combo chart, ROI trend

Key DAX measures created:

Measure Purpose
Gross Revenue Price × Units before discounts
Net Revenue Price minus Discount × Units
Total Units Sum of units sold
Marketing ROI Net Revenue ÷ Marketing Spend
Net Revenue PY Same period last year via SAMEPERIODLASTYEAR
Net Revenue YoY % Year-over-year growth rate

What to explore next:

  • Add Row-Level Security (RLS) to restrict data by Customer Segment for different report consumers.
  • Use Drill-through pages so clicking a category navigates to a detail page.
  • Create a Decomposition Tree visual to let users interactively break down Net Revenue by any dimension.
  • Export the report as a PDF or embed it in a SharePoint page using the embed code from the Service.
Power BI

More Blogs Like This