## Table of Contents

- [Introduction](#introduction)
- [The Dataset](#the-dataset)
- [Step 1 — Load the CSV into Power BI Desktop](#step-1--load-the-csv-into-power-bi-desktop)
- [Step 2 — Clean and Transform Data in Power Query](#step-2--clean-and-transform-data-in-power-query)
- [Step 3 — Create a Date Table](#step-3--create-a-date-table)
- [Step 4 — Build DAX Measures](#step-4--build-dax-measures)
- [Step 5 — Build the Report (Page 1: Sales Overview)](#step-5--build-the-report-page-1-sales-overview)
- [Step 6 — Build the Report (Page 2: Marketing Efficiency)](#step-6--build-the-report-page-2-marketing-efficiency)
- [Step 7 — Add Slicers for Interactivity](#step-7--add-slicers-for-interactivity)
- [Step 8 — Format and Polish](#step-8--format-and-polish)
- [Step 9 — Publish to Power BI Service](#step-9--publish-to-power-bi-service)
- [Summary](#summary)

---

## 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](https://powerbi.microsoft.com/en-us/downloads/)). 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"])
```

5. Click **OK**. A new column appears with proper date values.
6. Right-click the original `Date` column → **Remove**.
7. Rename `Date_Parsed` to `Date` by double-clicking the column header.
8. 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])
)
```

3. Click the checkmark to confirm.
4. Mark the table as a date table: right-click **Date Table** in the **Fields** pane → **Mark as date table** → select the `Date` column.
5. 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

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

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

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

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

```dax
-- 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.

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

### Year-over-year comparison

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

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

### Monthly trend helper

```dax
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](https://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.

A hands-on walkthrough for building a complete Power BI report from scratch using a retail sales dataset covering 2023–2025. Covers data loading, Power Query transformations, DAX measures, and building interactive visualizations.

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

## Table of Contents

- [Introduction](#introduction)
- [Sign Up and Access Power BI](#sign-up-and-access-power-bi)
- [Power BI Components](#power-bi-components)
- [Core Concepts](#core-concepts)
- [Connect Your First Data Source](#connect-your-first-data-source)
- [Build Your First Report](#build-your-first-report)
- [Key Skills to Learn](#key-skills-to-learn)
- [Share and Publish](#share-and-publish)
- [Embedding Power BI](#embedding-power-bi)
- [List of Terms](#list-of-terms)
- [Useful Resources](#useful-resources)
- [Conclusion](#conclusion)

## Introduction

Power BI is Microsoft's enterprise-grade business intelligence platform built on an in-memory semantic model. It is widely used in organizations that rely on Microsoft and Azure stacks for data modeling, reporting, and interactive dashboards. This guide will help you get started — from sign-up all the way to your first published report.



## Sign Up and Access Power BI

- **Power BI Desktop** — Free to download; no account required. Download from [powerbi.microsoft.com](https://powerbi.microsoft.com/en-us/downloads/).
- **Power BI Service** (cloud) — Sign up with a work or school Microsoft account at [app.powerbi.com](https://app.powerbi.com). Personal Microsoft accounts have limited service access.
- **Free vs Pro vs Premium**:
  - **Free** — personal use and exploration only.
  - **Pro** — required to share and collaborate with colleagues ($10/user/month).
  - **Premium Per User (PPU)** / **Premium Capacity** — higher refresh rates, larger datasets, paginated reports, and deployment pipelines.



## Power BI Components

| Component | Description |
|-----------|-------------|
| **Power BI Desktop** | Windows application for building data models and reports |
| **Power BI Service** | Browser-based cloud platform for sharing and collaboration |
| **Power BI Mobile** | iOS / Android app for consuming dashboards on the go |
| **Power BI Report Builder** | Tool for designing pixel-perfect paginated reports |
| **Power BI Gateway** | Bridge for refreshing on-premises data in the cloud |



## Core Concepts

Power BI is built around an **in-memory semantic model** — data is imported and compressed into memory for fast query performance, rather than querying the live source on every interaction.

- **Semantic Model / Dataset** — The underlying data model containing tables, relationships, and DAX measures.
- **Report** — The visual layer built on top of a dataset: charts, tables, maps, slicers, and more.
- **Dashboard** — A single-page canvas pinned from one or more reports (Service only).
- **Workspace** — A collaborative space in the Service for sharing datasets, reports, and dashboards across a team.
- **Dataflow** — A cloud-based ETL layer (Power Query in the Service) for reusable data transformations.



## Connect Your First Data Source

Power BI Desktop supports 100+ built-in connectors. Steps to connect:

1. Open **Power BI Desktop** → click **Get Data** on the Home ribbon.
2. Choose a connector — for example, **Excel**, **SQL Server**, **CSV**, or **Web**.
3. Enter connection details and credentials, then select the tables to load.
4. Click **Transform Data** to open the **Power Query Editor** for cleaning and shaping before loading.



![Step 1 – Click Get Data and choose a data source](/images/how-to-get-started-with-power-bi/powerbi-1-choose-data-source.png)
*Step 1 — On the Home ribbon, click **Get Data** and choose a data source from the list.*

![Step 2 – Browse the full connector list](/images/how-to-get-started-with-power-bi/powerbi-2-more-data-source.png)
*Step 2 — Click **More…** to browse the full connector list and find your specific data source.*

![Step 3 – Load a sample CSV file](/images/how-to-get-started-with-power-bi/powerbi-3-load-sample-csv-data.png)
*Step 3 — Select and load a sample CSV file to begin working with real data in the model.*

Common data sources:

- Excel / CSV / JSON files
- SQL Server / Azure SQL / Azure Synapse
- SharePoint / OneDrive
- REST APIs (Web connector)
- Azure Data Lake Storage
- Dataverse / Dynamics 365
- Google BigQuery / Snowflake / Databricks (via connectors)

## Build Your First Report

1. After loading data, switch to **Report view** (canvas icon in the left sidebar).
2. From the **Fields** pane (right side), drag a field onto the blank canvas to create a visual automatically.
3. Use the **Visualizations** pane to switch chart types (bar chart, line chart, map, table, card, etc.).
4. Add a **Slicer** visual to enable interactive date or category filtering.
5. Use the **Format** pane (paintbrush icon) to adjust colors, labels, titles, and borders.
6. Add multiple **Pages** (tabs at the bottom of the canvas) to organize content by topic or audience.

![Step 4 – Drag fields onto the canvas to start building your report](/images/how-to-get-started-with-power-bi/powerbi-4-start-create-report.png)
*Step 4 — In Report view, drag fields from the Fields pane onto the canvas to create your first visual automatically.*

![Step 5 – A simple report showing total sales summed by date](/images/how-to-get-started-with-power-bi/powerbi-5-simple-report-sum-by-date.png)
*Step 5 — A completed simple report displaying total sales summed by date as a bar chart.*

## Key Skills to Learn

### DAX (Data Analysis Expressions)

DAX is the formula language used to create calculated columns and measures in the semantic model.

- Similar in syntax to Excel formulas but designed for relational, columnar data.
- Measures are re-evaluated dynamically based on filter context.
- Essential functions to learn: `CALCULATE`, `SUM`, `SUMX`, `FILTER`, `ALL`, `RELATED`, `DIVIDE`, `DATEADD`, `TOTALYTD`.

```
-- Simple measure
Total Sales = SUM(Sales[Amount])

-- Year-to-date measure
YTD Sales = TOTALYTD([Total Sales], 'Date'[Date])

-- Filtered measure
Sales USA = CALCULATE([Total Sales], Region[Country] = "USA")
```



### Power Query / M Language

Power Query is the ETL layer — it runs before data enters the model.

- Low-code UI for most transformations (filter rows, remove duplicates, merge queries, unpivot columns).
- The underlying language is **M** (Power Query Formula Language).
- Common tasks: combining multiple files in a folder, splitting columns, changing data types, creating custom columns.



## Share and Publish

1. In Power BI Desktop, click **Publish** on the Home ribbon to upload to the Service.
2. In the Service ([app.powerbi.com](https://app.powerbi.com)), choose a **Workspace** to publish into.
3. Share a report by clicking **Share** and entering colleague email addresses (Pro license required for recipients).
4. Set up **Scheduled Refresh** in the dataset settings to keep imported data current (up to 8×/day on Pro, 48×/day on Premium).
5. Bundle multiple reports into an **App** to give end users a clean, curated experience.



## Embedding Power BI

Power BI offers several embedding options depending on your audience:

| Option | Audience | Auth |
|--------|----------|------|
| **Publish to Web** | Public internet | None (unauthenticated — do not use for sensitive data) |
| **Embed for your organization** | Internal portals | Azure Active Directory (AAD) |
| **Power BI Embedded (Azure)** | Customer-facing apps (ISV) | App-owns-data token |
| **iFrame embed** | Quick embeds | Power BI login |
| **REST API + JavaScript SDK** | Programmatic control | Flexible |

- For custom applications, use the **Power BI JavaScript SDK** (`powerbi-client`) to embed and interact with reports programmatically.
- **Power BI Embedded** is billed by Azure capacity (SKU A1–A8) or Premium capacity, not per user.



## List of Terms

- **DAX** — Data Analysis Expressions; formula language for measures and calculated columns.
- **Power Query / M** — ETL language for data transformation before loading into the model.
- **Semantic Model** — The logical data model (tables, relationships, measures) that reports are built on.
- **Measure** — A DAX formula that aggregates data dynamically based on report filters.
- **Calculated Column** — A DAX column computed row-by-row when data loads into the model.
- **Import Mode** — Default storage mode; data is loaded into in-memory columnar storage (VertiPaq).
- **DirectQuery** — Power BI sends live SQL queries to the source on every visual interaction.
- **Composite Model** — A dataset mixing Import and DirectQuery tables.
- **Row-Level Security (RLS)** — Restricts what data each user can see based on roles and DAX filters.
- **Gateway** — On-premises data gateway that allows the Service to refresh local data sources.
- **Paginated Report** — Print-ready, pixel-perfect report format; the successor to SSRS.
- **Dataflow** — Cloud-based Power Query transformations reusable across multiple datasets.
- **VertiPaq** — The in-memory columnar engine that powers Import mode performance.

## Useful Resources

- Official docs: https://learn.microsoft.com/en-us/power-bi/
- Free learning path (Microsoft Learn): https://learn.microsoft.com/en-us/training/powerplatform/power-bi
- DAX reference guide: https://dax.guide/
- Power Query M reference: https://learn.microsoft.com/en-us/powerquery-m/
- Community forum: https://community.fabric.microsoft.com/t5/Power-BI/ct-p/PBI_English
- Desktop download: https://powerbi.microsoft.com/en-us/downloads/
- Microsoft Certified | Power BI Data Analyst Associate: https://learn.microsoft.com/en-us/credentials/certifications/data-analyst-associate/?practice-assessment-type=certification

## Conclusion

Power BI is an excellent choice for organizations already in the Microsoft ecosystem. Start with **Power BI Desktop** (free download), connect a data source you already know — such as an Excel file or SQL Server table — and build a simple bar chart or table. Once comfortable with the Report view, explore DAX to write your first measure and Power Query to clean up your data. Then publish to the Service and set up a scheduled refresh to keep everything current.

Power BI is Microsoft's enterprise-grade business intelligence platform. This guide covers how to get started with Power BI Desktop, connect data sources, and build your first report.

Power BI

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

How to Get Started with Power BI