Build a Sales and Profit Report

Build an interactive Power BI report from Excel sales and profit data.

Introduction

30 Second Summary

A product can lead the sales chart while quietly losing money. Without profit context, an attractive result can point investment in the wrong direction.

In this project, you will turn an official Microsoft Excel sample into a one-page executive sales and profit report in Power BI Desktop. You will clean the data before using interactive visuals to support an evidence-based recommendation.

What You'll Build

You'll select a reporting period to watch every visual update around the same business question.

By the end of this project, you'll have:

An interview-ready report saved as a local .pbix file that you can open to demonstrate your analysis.
An executive KPI view that puts Sales, Profit, and Total Units Sold beside a product and segment comparison.
An evidence-based recommendation supported by a date-controlled monthly profit trend.
Secret Mission: Test whether the strongest sales recommendation still holds when performance is judged by country-level profit.

Are there any prerequisites?

You need your existing Microsoft Excel installation plus the free Power BI Desktop app on Windows. Basic familiarity with the Power BI Desktop interface is enough.

Before We Start

This first checkpoint defines who the report serves and the investment decision it must support. That focus keeps every later chart tied to sales and profitability.

Load the Financial Sample

Your executive report needs real business data before it can answer where investment should continue. Starting with a defined dataset keeps the project focused on that decision.

Microsoft's Financial Sample contains sales and profit data across market segments and countries or regions. Microsoft Excel reveals the workbook's source structure. Power BI Desktop turns its financials table into the report's starting point.

In this step, get ready to:

Download the official Financial Sample workbook into your portfolio project folder.
Confirm the workbook's business fields in Microsoft Excel.
Create a saved Power BI report from the financials table.

Download and inspect the source

Before Power BI can use the data, the source workbook needs a clear home inside your project folder. Its financials table contains the business fields that feed every later visual.

Open Microsoft's Financial Sample resource in your browser.
Download the workbook using the official Financial Sample file.

Your browser finishes with Financial Sample.xlsx in its download location. Good, the raw dataset is now on your computer.

Press the Windows key to open Windows Search.
Type File Explorer and press Enter to open it.

You will see File Explorer open with your folders available.

Locate Financial Sample.xlsx in your browser's download location.
Move Financial Sample.xlsx into your dedicated local portfolio project folder.

Your portfolio project folder now contains the official workbook.

Double-click Financial Sample.xlsx to inspect it in Microsoft Excel.

Excel opens the workbook with the populated business data visible.

Select any populated cell in the workbook table.
Use Excel's table tools to confirm its name is financials.
Review the header row for Sales, Profit, Product, Segment, Date, and Units Sold.

You will see financials as the table name. Its columns provide the sales, profitability, product, segment, date, and quantity evidence required by the report.

Workbook not in your folder?

Check your browser's download location for Financial Sample.xlsx. Move that file into the portfolio project folder before continuing.

Help me find and move the downloaded workbook.

Connect Power BI Desktop to Excel

Importing the financials table makes each workbook column available for analysis. The Data pane becomes your field list for building the report.

Press the Windows key to open Windows Search.
Type Power BI Desktop and press Enter to open it.

Power BI Desktop opens to its Home page.

Select Excel workbook on the Home page.
Return to your dedicated local portfolio project folder in the file picker.
Select Financial Sample.xlsx.
Select Open.

The Navigator window now lists the workbook content available for import.

Select the checkbox beside financials in Navigator.

The selected checkbox activates the import controls. Power BI may pause briefly while it reads the workbook after loading begins.

Select Load.

The report canvas returns when the import finishes. The Data pane now contains the loaded financials table.

Expand financials in the Data pane.

You will see fields including Sales, Profit, Product, Segment, Date, and Units Sold.

Unable to load the table?

Make sure the checkbox beside financials is selected in Navigator. Without that selection, Load remains inactive.

Help me troubleshoot the Financial Sample import.

Save the working report

Saving creates the local .pbix artifact that keeps the loaded table with the report canvas. A descriptive filename makes the project easy to find when you return to it.

Select File in Power BI Desktop.
Select Save.

The save window opens because this report does not have a local filename yet.

Select your dedicated local portfolio project folder as the save location.

The selected location now matches the folder containing Financial Sample.xlsx.

Enter Executive Sales and Profit Report in the filename field.
Select Save.

Power BI saves the report as Executive Sales and Profit Report.pbix.

Before you check, which two files do you expect to find in your portfolio project folder? Which fields should appear under financials?

Switch back to File Explorer from earlier.

You will see Financial Sample.xlsx beside Executive Sales and Profit Report.pbix in the portfolio project folder.

Switch back to Power BI Desktop.
Expand financials in the Data pane if it is collapsed.

You will see Sales, Profit, Product, Segment, Date, Units Sold, Month Name, and Country/Region in the field list. That confirms the raw source is loaded into your saved report.

Your official source is loaded into a saved raw report. Next, you will turn those fields into the first sales comparison on the canvas.

Build a Sales-Only First Draft

Your raw financials table is already loaded in Power BI Desktop. The next goal is to turn those rows into a first report view that answers a manager's sales question.

A quick sales view creates an early visible result. You will test whether one metric can support an investment decision.

In this step, get ready to:

Frame the first business question for your report.
Build a product and segment sales comparison.
Assess whether sales alone supports an investment recommendation.

Frame the first business question

An executive report needs a clear question before it needs visuals. Your first question is: which product and segment generate the most sales?

Return to the report canvas in the open Executive Sales and Profit Report.pbix report.
Select a blank area at the top of the report canvas.
Add a text box from the ribbon.
Enter Executive Sales and Profit Report in the text box.
Position the text box across the top of the canvas.

Good start. Your blank canvas now has an executive title that signals the report's purpose.

Title Missing From the Canvas?

Make sure the text box sits fully within the report canvas.
Increase the text contrast if the title blends into the canvas background.
Ask for help with the text box by selecting help me make my Power BI report title visible.

Create the first visible analysis

A clustered column chart places categories side by side. This shape makes product sales easy to compare across segments.

Power BI Desktop applies an implicit aggregation when a numeric field enters a visual. It summarizes the underlying Sales values for the categories shown.

Select a blank area below the report title.
In the Data pane, select Sales under financials.

You'll see Power BI Desktop create a visual that summarizes the sales values.

Select Product under financials.

You'll see separate product categories appear in the visual.

Select Segment under financials.

You'll see multiple products divided into segment-level bars.

Select the clustered column chart.
Use its formatting options to set the title to Sales by Product and Segment.

The new title ties the visual directly to your business question.

How Do the Fields Shape the Chart?

The Sales field determines the height of each bar.
The Product field creates the main comparison categories.
The Segment field splits each product into market groups.

Press Ctrl+S to save the updated report.

Your saved report now contains its first working analysis visual.

Chart Not Using All Three Fields?

Confirm that Sales is selected in the Data pane.
Confirm that Product is selected in the Data pane.
Confirm that Segment is selected in the Data pane.
Ask for help by selecting help me fix my sales comparison chart.

Inspect the sales-only limitation

Tall bars can create a strong first impression. Now you will test whether that impression provides enough evidence for an investment decision.

Before you inspect the chart, do you think the tallest sales bar is enough to choose where the company should invest?

Use the chart's sort control to order the bars by Sales from highest to lowest.
Select the tallest bar in the chart.
Identify the product represented by the selected bar.
Identify the segment represented by the selected bar.
Check whether the chart displays Profit values.
Check whether the chart shows performance over time.

The chart presents an apparent sales leader. It does not show Profit.

The chart also lacks a time view. This intended shortfall proves that sales alone cannot support the final recommendation.

Why Does Sales Alone Fall Short?

Sales shows how much revenue a product or segment generates. It does not reveal how much profit remains.

A total sales result can also hide changes across months or years. A defensible recommendation needs evidence beyond one headline metric.

Before your final check, do you expect the chart to show one overall total or multiple products split by segment?

View the full report canvas.
Confirm the text title reads Executive Sales and Profit Report.
Confirm the chart title reads Sales by Product and Segment.
Confirm the chart displays multiple products split by Segment.
Press Ctrl+S to save Executive Sales and Profit Report.pbix.

That's your first visible report draft working. You can compare sales leaders while clearly explaining why the evidence remains incomplete.

Your first report view is saved. Next up, you'll clean the raw fields so every later conclusion rests on consistent data.

Clean the Data in Power Query

Your first-draft report now makes sales leaders visible. That early chart gives you something concrete to improve.

The chart still relies on raw values from the financials table. That weakens any decision based on it.

Power Query gives each field a consistent meaning. This step also limits the product scope to active products.

In this step, get ready to:

Correct the quantity data type.
Standardize the report labels.
Filter the discontinued product from the analysis.

Correct the quantity data type

A data type tells Power BI Desktop how to interpret a column. Units Sold represents countable items, so Whole Number matches its business meaning.

Return to the Home ribbon in Power BI Desktop.
Select Transform data.
Select financials in the left pane of Power Query Editor.
Select the Units Sold column.
Select the Transform tab.
Open the Data Type menu.
Select Whole Number.
Select Replace current.

You should see Units Sold set to Whole Number. The Applied Steps pane records the type change.

Why Use a Whole Number?

A whole-number type keeps fractional quantities out of the field. Future totals can now treat each value as a discrete unit.

Standardize the labels

Category labels need consistent casing before they support trustworthy comparisons. Uppercase Segment values keep every market segment under one label.

Select the Segment column.
Select the Transform tab.
Open the Format menu.
Select UPPERCASE.

The preview should show every value in Segment in uppercase.

The Month Name header can be shortened without changing its values. The name Month makes the field easier to scan later.

Right-click the Month Name column header.
Select Rename.
Enter Month.
Press Enter.

You should see a column named Month in place of Month Name.

Apply the business rule

A business rule determines which rows belong in the analysis. The sample scenario identifies Montana as discontinued.

Open the filter menu in the Product column.
Clear Montana.
Apply the filter selection.

You should no longer see rows for Montana in the data preview.

Confirm in the Applied Steps pane that Units Sold uses Whole Number.
Confirm that Segment values appear in uppercase.
Confirm that Month Name is now named Month.
Confirm that Montana is absent from the Product preview.

Before you apply these transformations, which product do you expect to disappear from the existing sales chart?

Select the Home tab in Power Query Editor.
Select Close & Apply.

Power BI Desktop returns to the report canvas. The Sales by Product and Segment chart should no longer include Montana.

Still Seeing Montana?

Return to the Home ribbon in Power BI Desktop.
Select Transform data.
Check that Montana is cleared in the Product filter.
Select Close & Apply again.

Help me diagnose why Montana still appears in my chart.

Press Ctrl+S to save Executive Sales and Profit Report.pbix.

That cleanup is locked in: Executive Sales and Profit Report.pbix now uses the filtered financials table.

Your financials table is ready for analysis. Next, you will add profit context so the report can test whether its sales leaders are financially strong.

Add Profit Context and KPI Cards

The first draft shows where sales are strongest. The cleaned product scope also keeps the discontinued Montana product out of view.

That view still hides whether strong sales create strong profit. It also hides how many units drove the result.

Power BI Desktop can close that gap with KPI cards. A monthly profit trend shows when financial performance changes.

In this step, get ready to:

Create a reusable DAX measure for total units sold.
Add three executive KPI cards.
Plot monthly profit with Year and Month visible.

Create a reusable units measure

The Units Sold column stores quantity at row level. A measure recalculates that quantity under the current filter context.

On the Home ribbon, select New measure.
Enter this formula in the formula bar:

Total Units Sold = SUM(financials[Units Sold])

What Does This Measure Do?

Total Units Sold names a calculation that other visuals can reuse.
SUM(financials[Units Sold]) adds every number in the Units Sold column within the current report selection.
The result recalculates whenever a report filter changes its context.

Commit the measure by selecting the check mark.
Save Executive Sales and Profit Report.pbix by pressing Ctrl+S.
Expand financials in the Data pane.
Confirm Total Units Sold appears beneath the table fields.

That reusable measure is ready for the report. Any visual that uses it can respond to the report's current filters.

Measure Missing From the Data Pane?

Confirm that you selected the check mark after entering the formula. An uncommitted formula does not appear beneath financials.

Check that the formula uses the table name financials plus the column name Units Sold exactly.

Help me troubleshoot my Total Units Sold measure.

Add executive KPI cards

KPI cards make headline values easy to scan. You will create one card for each executive measure.

Select a blank area of the report canvas.
Choose the Card visual.
Add Sales from financials to the card.
Open the field menu for Sales.
Select Sum.

You'll see the summed Sales value on the new card.

Select another blank area of the report canvas.
Choose the Card visual.
Add Profit from financials to the card.
Open the field menu for Profit.
Select Sum.

You'll see the summed Profit value on the second card.

Select another blank area of the report canvas.
Choose the Card visual.
Add Total Units Sold from financials to the card.
Save Executive Sales and Profit Report.pbix by pressing Ctrl+S.

You have broken through the sales-only ceiling. The report now puts activity beside financial performance.

Cards Showing Unexpected Values?

Check that the Sales card uses Sum.
Check that the Profit card uses Sum.
Confirm that the third card uses the Total Units Sold measure.

Help me fix the values in my KPI cards.

Plot profit over time

A profit total hides the timing behind the number. A Date hierarchy makes year-to-month movement visible.

Select a blank area of the report canvas.
Choose the Line chart visual.
Add Profit from financials to the visual.
Add Date from financials to the visual.
Select Date Hierarchy for the Date field.
Keep Year visible in the hierarchy.
Keep Month visible in the hierarchy.
Save Executive Sales and Profit Report.pbix by pressing Ctrl+S.

Before you inspect the line, which month and year do you expect to contain the highest profit?

Inspect the highest point in the line chart.
Review the complete report canvas.

You'll find the profit peak at December 2014. This confirms that the chart exposes timing that the sales-only view could not show.

What Should I See?

The clustered column chart remains titled Sales by Product and Segment.
The Sales card displays a summed value.
The Profit card displays a summed value.
The Total Units Sold card displays the reusable measure.
The line chart displays Profit through Year plus Month.

Line Chart Missing Monthly Movement?

Confirm that the visual uses Profit plus the Date Hierarchy. A single date level can hide the intended monthly pattern.

Check that Year remains visible. Confirm that Month also remains visible.

Help me troubleshoot my monthly Profit line chart.

Your report now compares sales with profit, units, and timing. Next, you will add interactivity plus a decision-focused recommendation.

Make the Report Interview-Ready

Your cleaned Power BI Desktop report now compares sales with profit. The next goal is a page that a hiring manager can scan during an interview.

Correct analysis still needs clear navigation before it becomes a credible portfolio artifact. A decision-focused explanation turns the finished page into evidence you can defend.

In this step, get ready to:

Add a date slicer that filters every report visual.
Organize the page around a clear visual hierarchy.
Finish the report with an evidence-based recommendation.

Add a date slicer

A slicer is an interactive control that narrows the reporting period. It changes the filter context across connected visuals.

Expand financials in the Data pane.
Drag Date from the Data pane onto a blank area of the canvas.
Choose Slicer for the new visual.
Choose a date style that lets you filter the report to one full year.
Use the slicer to show only 2014.

The three KPI cards update. Both charts shift to the selected reporting period.

That interaction is working. Your report now answers a time-specific question without rebuilding a chart.

Are Some Visuals Staying Unchanged?

Confirm that the slicer uses Date from financials.
Remove any visual-level date filter that fixes a card or chart to another period.

Help me find why a Power BI visual is ignoring my date slicer.

Create a clear visual hierarchy

Visual hierarchy guides the reader from headline results to supporting evidence. Canvas alignment can feel fiddly because every visual must remain readable on one page.

Select View on the ribbon.
Apply the Executive theme.

You will see the report adopt a consistent set of colors plus text styles.

Add a Text box to the top of the canvas.
Enter Executive Sales and Profit Report as the report title.

The page now has a clear entry point for anyone seeing the report for the first time.

Arrange the three KPI cards in one row beneath the report title.
Place the clustered column chart beneath the KPI row.

The headline numbers now appear before the detailed sales comparison.

Place the monthly profit line chart beside the sales comparison.
Keep Sales by Product and Segment as the clustered column chart title.

The lower half of the page now connects product performance with profit movement over time.

Rename the line chart to How Has Profit Changed Over Time?.
Rename the date slicer to Which Reporting Period Should We Review?.

You can now scan the title first. The KPI row leads naturally into the two analysis charts.

Write and verify the recommendation

Data storytelling turns the report into a business decision. Your recommendation needs a clear choice supported by the visible sales pattern plus the profit response.

Return the Date slicer to the full reporting period.
Select a leading bar in the Sales by Product and Segment chart.

The KPI cards respond to the selected product-segment result. The Profit card shows whether strong sales also produce profit.

Read the filtered Profit card while the leading bar remains selected.
Repeat the same check for the other leading bars.

The result should support Paseo as the product recommendation. It should support the Small Business plus Government segments.

Add a new Text box beneath the analysis charts.
Write a first sentence recommending continued investment in Paseo across the Small Business plus Government segments.

Your report now states the decision instead of leaving the reader to infer it.

Write a second sentence that cites the visible sales pattern plus the filtered Profit card.
Add a final sentence explaining that fictional sample data cannot predict future performance.

The recommendation now gives the reader a decision, supporting evidence, plus a clear limitation.

Before the final test, predict whether all three KPI cards plus both charts will change when you select 2013.

Use the Date slicer to show only 2013.

You will see new values in all three KPI cards. Both analysis charts update to show only 2013.

Press Ctrl+S to save Executive Sales and Profit Report.pbix in the portfolio project folder.

Power BI Desktop writes the finished page back to Executive Sales and Profit Report.pbix. Your local report is now polished enough to demonstrate in an interview.

Secret mission

Challenge the Obvious Recommendation

You will add a geography-level profit chart. Then you will use its leading result to challenge the recommendation. Your final report will state whether the investment choice still holds.

Clean Up Your Resources

This project stores its report files locally with no ongoing costs. Choose the cleanup option that matches your next move.

Resources you used:

Your local portfolio project folder containing Financial Sample.xlsx and Executive Sales and Profit Report.pbix.

Keep everything running

No action is needed. This option keeps the report open in Power BI Desktop for continued work.

Leave Financial Sample.xlsx and Executive Sales and Profit Report.pbix together in the local portfolio project folder.

Pause - I'll come back to this later

Closing the active report frees the Power BI Desktop session. Your local files remain ready for later.

Save the latest version of Executive Sales and Profit Report.pbix by pressing Ctrl+S.
Close Power BI Desktop.
Confirm the local portfolio project folder still contains Financial Sample.xlsx and Executive Sales and Profit Report.pbix.

Delete - I don't want to use this again

Deleting the folder removes only this local project. Your Microsoft Excel and Power BI Desktop installations stay in place.

Close Power BI Desktop.
Use your Windows file browser to return to the local portfolio project folder from earlier.
Select the folder containing Financial Sample.xlsx and Executive Sales and Profit Report.pbix.
Move the selected folder to the Recycle Bin by pressing Delete.
Confirm the local portfolio project folder no longer appears in your Windows file browser.
Open the Recycle Bin from your Windows desktop.
Empty the Recycle Bin.
Confirm Financial Sample.xlsx and Executive Sales and Profit Report.pbix no longer appear in the Recycle Bin.

Nice Work!

You did it! You turned an official Microsoft Excel workbook into the locally saved Executive Sales and Profit Report.pbix in Power BI Desktop. Your report now connects interactive analysis to an evidence-based investment recommendation.

You've learned how to:

Converted the raw financials table into a consistent analysis model through Power Query transformations.
Created the Total Units Sold DAX measure for reusable quantity analysis. Built an interactive executive report where the date slicer updates the KPI cards plus both core charts.
Turned sales evidence plus profit evidence into an executive recommendation that distinguishes sales volume from financial performance.
Secret Mission: Stress-tested the recommendation with a country-level profit analysis. Used cross-filtering to decide whether the original investment choice still holds.

Ready to quiz yourself?