How to Create a Report in Power BI Desktop [With Real Scenarios]

A sales manager often starts with a familiar problem: several Excel files, a SharePoint list, and no quick way to see monthly sales, top products, or low-performing regions. I have worked with teams that spent hours combining files and updating summary tables before every review meeting.

That process changes quickly when you create a report in Power BI Desktop. You can connect to your Excel or SharePoint data, clean it once, build interactive visuals, and give managers a report they can filter themselves.

This step-by-step Power BI tutorial uses a retail sales dashboard example and covers the full process, from loading data through publishing and sharing.

Before You Create a Report in Power BI Desktop

Before you create a report in Power BI Desktop, prepare data that supports analysis. For this example, assume a mid-sized retail company tracks sales in an Excel workbook or a SharePoint Online list.

The sales data should include fields such as:

• Order Date
• Order ID
• Product Name
• Product Category
• Region
• Salesperson
• Quantity
• Sales Amount
• Cost Amount

Each row should represent one transaction. This structure matters because Power BI Desktop works best when every record has a clear business meaning. Avoid merged cells, blank header rows, totals inside the source data, and inconsistent date formats.

For Excel, convert your range into an Excel Table before connecting. Click inside the data, select Insert > Table, and give it a meaningful name such as Sales. A proper table expands when new rows arrive, which makes refreshes far more reliable.

For SharePoint, keep column names simple and clear. Use a Number column for quantity, Currency for sales values, and Date and Time for order dates. If you are starting with SharePoint lists, review this guide on how to create and manage a SharePoint list.

Pro Tip: I have found that most report issues begin in the source data, not the visual. Spend a few minutes cleaning headers and data types before loading anything into Power BI.

Create a Report in Power BI Desktop From Excel

Excel remains a common starting point for small business reporting. It works well when a team stores transactional data in a controlled workbook and updates it regularly.

Connect to the Excel workbook

Open Power BI Desktop and select Get data from the Home tab.

1. Select Excel workbook.
2. Browse to your workbook and select Open.
3. In the Navigator window, select the Sales table.
4. Choose Transform Data instead of loading immediately.

Choose Transform Data because it opens Power Query Editor. Power Query is the data preparation area in Power BI Desktop. You use it to clean, rename, filter, split, and format columns before the data enters your report.

If your sales file contains extra columns such as Notes, Internal Comments, or Created By, remove them now. Smaller tables refresh faster and make the data model easier to understand.

Clean the sales data in Power Query

Inside Power Query Editor, check each column’s data type. Power BI may guess correctly, but do not rely on that guess.

For the retail sales example, use these data types:

• Order Date: Date
• Order ID: Text
• Product Name: Text
• Region: Text
• Quantity: Whole Number
• Sales Amount: Fixed Decimal Number or Currency
• Cost Amount: Fixed Decimal Number or Currency

To change a type, select the column, choose the type icon beside the column name, and select the correct option. This step is important because Power BI calculations depend on data types. A sales amount stored as text will not calculate correctly in a measure.

Rename unclear columns while you are here. For example, rename Amt to Sales Amount and ProdNm to Product Name. Clear names make report development easier, especially when several people maintain the file.

If you need to add or modify fields during data preparation, see these practical Power Query column examples.

When the table looks clean, click Close & Apply. Power BI loads the data into the report.

Create a Report in Power BI Desktop From SharePoint

A SharePoint Online list works well when multiple people enter or update operational data. For example, a sales operations team may maintain product, vendor, inventory, or order status information in SharePoint.

Connect to a SharePoint Online list

In Power BI Desktop, select Home > Get data > More.

1. Select Online Services.
2. Choose SharePoint Online List.
3. Click Connect.
4. Enter the SharePoint site URL, not the full list URL.
5. Sign in with an account that has access to the site.
6. Select the required list in the Navigator window.
7. Click Transform Data.

Power BI displays available lists from that SharePoint site. Select only the lists needed for the report. For a sales dashboard, you may choose a Sales Orders list, a Products list, and a Regions list.

Expand lookup and person columns

SharePoint lookup, person, and choice columns often appear as Record values in Power Query. You must expand them before building visuals.

Select the expand icon on the column header. Then choose only the subfields you need, such as Value, Title, DisplayName, or Email.

For example, if the Product field is a SharePoint lookup column, expand it and select the product name. If you leave it as a record, your chart cannot display a readable product label.

You should also remove SharePoint system fields that do not help the report, such as Version, Attachments, Content Type, or Edit links. Retain fields only when users need them for reporting or filtering.

Validate the SharePoint data types

SharePoint connectors often bring fields into Power Query with the Any type. Correct them before loading.

Set:

• Sales dates to Date
• Quantity to Whole Number
• Sales values to Currency
• Product and region names to Text

If dates look wrong after loading, use this guide to convert yyyymmdd values to proper dates in Power BI.

Build a Simple Data Model

A data model connects related tables so Power BI can filter and calculate across them. For a beginner report built from one Excel table, Power BI may only have one table. That is fine for an initial dashboard.

For a more reliable sales dashboard, split your model into a fact table and supporting dimension tables.

• Sales: The fact table that contains transactions and numeric values.
• Product: A dimension table with product name, category, and brand.
• Region: A dimension table with region and territory details.
• Date: A dimension table that supports monthly, quarterly, and yearly analysis.

Open Model view from the left navigation. Create one-to-many relationships from each dimension to the Sales table. For example, Product[Product ID] should connect to Sales[Product ID].

A one-to-many relationship means one product can appear in many sales transactions. This design is called a star schema because the Sales table sits in the middle and the lookup tables surround it.

Avoid linking every table to every other table. That creates confusing filter paths and incorrect totals. If you notice blank values in visuals, inspect your keys and relationships using this guide on filtering blank values in Power BI.

Add a Date Table for Time Analysis

A proper Date table helps you compare months, quarters, and years. It also supports time intelligence calculations such as last year’s sales.

Create a new table by selecting Modeling > New table. Add this DAX formula:

Date =
CALENDAR(
    MIN(Sales[Order Date]),
    MAX(Sales[Order Date])
)

This formula creates one row for every date between the earliest and latest order date in your Sales table.

Next, add useful date columns:

Year = YEAR('Date'[Date])

Month Number = MONTH('Date'[Date])

Month Name = FORMAT('Date'[Date], "MMMM")

Year Month = FORMAT('Date'[Date], "YYYY MMM")

Then select the Date table, choose Table tools, and click Mark as date table. Select the Date column when Power BI asks for the date field.

Marking the table tells Power BI that this table controls calendar logic. Create a relationship from Date[Date] to Sales[Order Date].

For a deeper walkthrough, read how to create a calendar table using the DAX CALENDAR function.

Create DAX Measures for Your KPIs

A measure is a calculation that responds to filters, slicers, and visuals. Measures work better than calculated columns for totals, averages, and KPIs because Power BI calculates them when users interact with the report.

Right-click the Sales table and select New measure.

Create the main sales measure:

Total Sales =
SUM(Sales[Sales Amount])

This measure adds the Sales Amount values in the current filter context. If a user chooses the West region in a slicer, it returns only West region sales.

Create a quantity measure:

Total Quantity =
SUM(Sales[Quantity])

Create a profit measure:

Total Profit =
SUM(Sales[Sales Amount]) - SUM(Sales[Cost Amount])

Create last year sales:

Sales LY =
CALCULATE(
    [Total Sales],
    SAMEPERIODLASTYEAR('Date'[Date])
)

The CALCULATE function changes the filter context. SAMEPERIODLASTYEAR shifts the selected date range back one year. If the visual shows March 2026 sales, this measure returns March 2025 sales.

Create year-over-year growth:

Sales YoY % =
DIVIDE(
    [Total Sales] - [Sales LY],
    [Sales LY]
)

The DIVIDE function avoids errors when last year’s sales equal zero. Format this measure as a percentage.

Pro Tip: In my experience, a report becomes easier to maintain when all core calculations are measures. Avoid creating a new calculated column just to show a total on a card.

Design the Sales Dashboard

Now move to Report view. A strong report answers a business question without making users hunt for data.

For this example, create one page called Sales Overview.

Add KPI cards

Use Card visuals at the top of the report. Add:

• Total Sales
• Total Profit
• Total Quantity
• Sales YoY %

Cards give managers a quick view of the numbers that matter. Keep four or fewer cards in one row to avoid clutter.

Add a monthly sales trend

Add a Line chart.

• X-axis: Date[Year Month]
• Y-axis: Total Sales

Sort the axis by a real date field or month number. Alphabetical month names create the wrong sequence, such as April, August, December, and February.

A line chart shows movement over time. It helps a manager spot a slow month, seasonal demand, or sudden growth.

Add sales by region and product

Add a Clustered bar chart.

• Y-axis: Region[Region Name]
• X-axis: Total Sales

Then add another bar chart:

• Y-axis: Product[Product Category]
• X-axis: Total Sales

Bar charts make comparisons easy. Use them for categories, regions, salespeople, or product lines. Sort each chart by Total Sales in descending order so the highest performer appears first.

Add slicers for interaction

A Slicer lets users filter report visuals without editing the report.

Add slicers for:

• Year
• Region
• Product Category
• Salesperson

Use a dropdown design when your slicer has many choices. It saves space and keeps the dashboard clean. This detailed guide explains how to add a dropdown slicer in Power BI.

Make sure every visual responds to the slicers. Select a slicer, go to Format > Edit interactions, and confirm that it filters the correct charts.

Publish and Share the Report

Save the file as a .pbix file first. Use a meaningful name such as Retail-Sales-Dashboard.pbix.

To publish, select Publish from the Home ribbon and sign in to your Power BI account. Choose a workspace, which is a shared area in Power BI Service where teams store reports, datasets, dashboards, and apps.

Choose a workspace based on the audience:

• Use My workspace for personal testing.
• Use a team workspace for department reports.
• Use a controlled workspace for production reports.

After publishing, open the report in Power BI Service. Check the layout, refresh the data, and configure access for the correct users.

If your report uses SharePoint Online lists or files stored in SharePoint, scheduled refresh usually works without an on-premises gateway. If the Excel file sits on your laptop or a local network drive, you may need a gateway before Power BI Service can refresh it.

Things to Consider

• Use a star schema: Keep transaction data in a central Sales table and connect it to dimensions such as Date, Product, and Region. This model produces more reliable filtering and calculations.
• Use measures for KPIs: Create DAX measures for sales, profit, and growth rates. Measures react to slicers, while calculated columns increase model size.
• Check data types early: Set dates, currency values, and quantities correctly in Power Query before building visuals or writing DAX.
• Avoid too many visuals: A dashboard should answer key questions quickly. Use a few meaningful cards, charts, and slicers instead of filling every empty space.
• Protect sensitive data: Use Row-Level Security when regional managers should only view their own region’s data.
• Test refresh before sharing: Refresh the report in Power BI Service after publishing. Check credentials, source paths, and list permissions before users depend on it.

Frequently Asked Questions

Report in Power BI Desktop becomes straightforward when you begin with clean Excel or SharePoint data, build a simple data model, and use measures for your business KPIs. Start with one focused sales dashboard, validate the numbers, and improve the design as your users ask better questions. I hope you found this article helpful.

You May Also Like

• Power BI date slicer only shows dates with data
• Power BI Date Slicer by Month
• How to sort a Power BI slicer by measure
• Power BI DAX MIN date function with examples
• Export a Power BI report to Excel
• Power BI Free vs Pro vs Premium comparison