Creating an Impressive Report in Power BI Desktop from an Excel Workbook

In this tutorial, you will create an impressive report in just 20 minutes!

Imagine your manager has requested an executive summary of your latest sales figures, specifically:

• Which month and year generated the highest profit?

• In which country or region is the company experiencing the most success?

• Which product and market segment should the company focus on for future investments?

Using the sample finance workbook, we’ll build this report quickly. Let’s dive in!

What You Will Learn

• How to download sample data.

• How to prepare your data with a few transformations.

• How to create a report featuring a title, three visuals, and a slicer.

• How to publish your report to the Power BI Service for sharing.

Prerequisites

Before starting, make sure you have Power BI Desktop installed. If you plan to publish your report and don’t have an account yet, sign up for a free trial.

Getting Data

You can obtain the data through one of two methods: connect directly to the sample in Power BI Desktop or download the sample workbook.

Option 1: Connect to Sample Data in Power BI Desktop

1. Open Power BI Desktop and select Learn with sample data from the Home page.

2. In the dialog that appears, click Load sample data.

Option 2: Download the Sample Workbook

1. Download the Financial Sample Excel workbook.

2. Open Power BI Desktop and select Excel workbook on the Home page.

3. Navigate to the saved workbook and click Open.

Preparing Your Data

In the Navigator, you can choose to transform or load your data. The Navigator shows a preview, allowing you to confirm the data range. Numeric data types are italicized. To enhance visual readability, we’ll transform the data. Every transformation you make in Power Query Editor will be listed under Applied Steps.

1. Check the box for the financials table and click Transform Data.

Once in Power Query Editor:

• Select the Units Sold column and change its data type to Whole Number to eliminate decimals, which don't make sense for unit sales.

• Format the Segment column to UPPERCASE for consistency.

• Rename the Month Name column to simply Month.

• Filter out the Montana product, which was discontinued, to prevent confusion.

After making these transformations, click Close & Apply to get your data ready for reporting.

Extra Credit: Write DAX Expressions

DAX (Data Analysis Expressions) can enhance your data modeling. Let’s create a measure and a table.

Create a New Measure

1. On the Home ribbon, select New measure.

2. Enter the following expression to sum the Units Sold:

   dax

   Copy

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

3. Click the check mark to save your measure.

Create a New Table

1. Click the Table view icon.

2. On the Home ribbon, select New table.

3. Enter this expression to create a Calendar table:

   dax

   Copy

    Calendar = CALENDAR(DATE(2013,01,01), DATE(2014,12,31))
   

4. Click the check mark to save.

Next, go to Model view and create a relationship between the Date fields in your financials and Calendar tables. Drag the Date field from the financials table over to the Date field in the Calendar table to link the two tables and establish a relationship. In the New Relationship dialog that appears, click Save to confirm the settings that Power BI has automatically populated for you.

Building Your Report

Now that your data is transformed and loaded, it’s time to create the report. Switch to Report view in the left pane.

Visual 1: Add a Title

1. On the Insert ribbon, select Text box and type “Executive Summary -- Finance Report”.

2. Format the text to bold and set the font size to 20.

Visual 2: Profit by Date

1. Drag the Profit field to the report canvas to create a column chart.

2. Add the Date field to the same visual to show monthly profits.

3. Change the visualization type to Line chart for better clarity, revealing that December 2014 had the highest profit.

Visual 3: Profit by Country/Region

1. Drag the Country field to create a map visual.

2. Add the Profit field to the map to visualize relative profits, indicating Europe, particularly France and Germany, as top performers.

Visual 4: Sales by Product and Segment

1. Arrange the previous visuals side by side.

2. Select the Sales, Product, and Segment fields to create a clustered column chart that suggests continued investment in the Paseo product and targeting the Small Business and Government segments.

Visual 5: Year Slicer

1. Create a slicer using the Date field from the financials table to filter by month and year.

2. Repeat the process using the Date field from the Calendar table to allow for more detailed filtering options.

Extra Credit: Format the Report

To polish your report:

Apply a Theme

1. On the View ribbon, select the Executive theme.

Enhance Visuals

1. Format each visual, adjusting titles and sizes for clarity and aesthetics.

Add Background Shapes

1. Use rectangles to create background shapes for titles and visuals, adjusting colors and transparency for a professional look.

Final Report

Your completed report should clearly answer your manager's questions:

• The month with the highest profit: December 2014.

• The most successful region: Europe (France and Germany).

• Recommended investment: Paseo product and Small Business and Government segments.

Save Your Report

To save your work, select File > Save or press Ctrl + S. If it’s your first time saving, you’ll be prompted to choose a name and location.

Now you’re ready to share your insights!