Tutorial: From dimensional model to stunning report in Power BI Desktop

APPLIES TO: Power BI Desktop  Power BI service

In this tutorial, you start with a dimensional model and build a beautiful report from start to finish in 45 minutes.

You work at AdventureWorks and your manager wants to see a report on your latest sales figures. They've requested an executive summary of:

• Which day had the most sales in February 2019?
• Which country/region is the company seeing the most success in?
• Which product category and reseller business types should the company continue to invest in?

Using our AdventureWorks Sales sample Excel workbook, we can build this report in no time. Here’s what the final report will look like.

Want to see the finished product? You can also download the completed Power BI .pbix file.

Let’s get started!

In this tutorial, you'll learn to:

Prerequisites

• Before you start, you need to download Power BI Desktop.
• If you're planning to publish your report to the Power BI service and you aren't signed up yet, sign up for a free trial.

Get data: Download the sample

1. Download the AdventureWorks Sales sample Excel workbook.

2. Open Power BI Desktop.

3. In the Data section of the Home ribbon, select Excel.

4. Navigate to where you saved the sample workbook, and select Open.

Prepare your data

In the Navigator pane, you have the option to transform or load the data. The Navigator provides a preview of your data so you can verify that you have the correct range of data. Numeric data types are italicized. In this tutorial, we're going to transform the data before loading.

Select all tables, and choose Transform Data. Make sure not to select the sheets (labeled _data).

Check that the data types of the columns match the data types in the following table. To let Power BI detect data types for you, select a query, then select one or more columns. On the Transform tab, select Detect Data Type. To make any changes to the detected data type, on the Home tab, select Data Type, then select the appropriate data type from the list.

Back on the Home tab, select Close & Apply.

Model your data

The data you loaded is almost ready for reporting. Let’s inspect the data model and make some changes.

Select Model View on the left.

Your data model should look like the following image, with each table in a box.

Create relationships

This model is a typical star schema that you might see from data warehouses: It resembles a star. The center of the star is a Fact table. The surrounding tables are called Dimension tables, which are related to the Fact table with relationships. The Fact table contains numerical information about sales transactions, such as Sales Amount and Product Standard Cost. The Dimensions provide context so you can, among other things, analyze:

• What Product was sold...
• to which Customer...
• by which Reseller...
• in which Sales Territory.

If you look closely, you notice that all Dimension tables are related to the Fact with a Relationship, except for the Date table. Let’s add some relationships to Date now. Drag the DateKey from the Date table to OrderDateKey on the Sales table. You've created a so-called "one-to-many" relationship from Date to Sales, as indicated by the 1 and the asterisk * (many) at the two ends of the line.

The relationship is "one-to-many" because we have one or more Sales orders for a given Date. If each date had only one Sales order, the relationship would be "one-to-one". The little arrow in the middle of the line indicates the "cross-filtering direction." It indicates that you can use values from the Date table to filter the Sales table, so the relationship allows you to analyze when a Sales order was placed.

The Sales table contains more information about dates related to Sales orders, such as Due Date and Ship Date. Let’s add two more relationships to the Date table by dragging:

• DateKey to DueDateKey
• DateKey to ShipDateKey

You notice that the first relationship, on OrderDateKey, is active, shown by the continuous line. The other two are inactive, shown by the dashed lines. Power BI uses the active relationship by default to relate Sales and Date. Hence, a sum of SalesAmount is calculated by Order Date, not Due Date or Ship Date. You can influence this behavior. See Extra credit: Write a measure in DAX later in this tutorial.

Hide key columns

The typical star schema contains several keys that hold the relationships between Facts and Dimensions. Normally we don't want to use any key columns in our reports. Let’s hide the key columns from view, so the Fields List shows fewer fields, and the data model is easier to use.

Go over all tables and hide any column whose name ends with Key:

Select the Eye icon next to the column and choose Hide in report view.

You can also select the Eye icon next to the column in the Properties pane.

Hidden fields have this icon, an eye with a line through it.

Hide these fields.

Your data model should now look like this data model, with relationships between Sales and all the other tables, and all the key fields hidden:

Create hierarchies

Now that our data model is easier to consume because of the hidden columns, we can add a few hierarchies to make the model even easier to use. Hierarchies enable easier navigation of groupings. For example, cities are in a State or Province, which is in a Country or Region.

Create the following hierarchies.

1. Right-click the highest level, or the least granular, field in the hierarchy and choose Create hierarchy.

2. In the Properties pane, set the Name of the hierarchy and set the levels.

3. Then Apply Level Changes.

   

You can also rename levels in a hierarchy in the Properties pane after you add them. You'll need to rename the Year and Quarter level of the Fiscal hierarchy in the Date table.

Here are the hierarchies you need to create.

Your data model should now look like the following data model. It has the same tables, but each dimension table contains a hierarchy:

Rename tables

To finish modeling, let's rename the following tables in the Properties pane:

This step is necessary because Excel table names can't contain spaces.

Now your final data model is ready.

Extra credit: Write a measure in DAX

Writing measures in the DAX formula language is super powerful for data modeling. There's lots to learn about DAX in the Power BI documentation. For now, let's write a basic measure that calculates the total sales amount by due date on the sales order instead of the default order date. This measure uses the USERELATIONSHIP function to activate the relationship between Sales and Date on DueDate for the context of the measure. It then uses CALCULATE to sum the Sales Amount in that context.

1. Select Table view on the left.

   

2. Select the Sales table in the Fields list.

   

3. On the Home ribbon, select New Measure.

4. Select or type this measure to calculate the total sales amount by due date on the sales order instead of the default order date:

   Sales Amount by Due Date = CALCULATE(SUM(Sales[Sales Amount]), USERELATIONSHIP(Sales[DueDateKey],'Date'[DateKey]))
   

5. Select the check mark to commit.

   

Build your report

Now that you’ve modeled your data, it's time to create your report. Go to Report View. In the Fields pane on the right, you see the fields in the data model you created.

Let’s build the final report, one visual at a time.

Visual 1: Add a title

1. On the Insert ribbon, select Text Box. Type Executive Summary – Sales Report.

2. Select the text you typed. Set the font size to 20 and Bold.

   

3. In the Format text box pane, under Effects, toggle the Background to Off.

4. Resize the box to fit on one line.

Visual 2: Sales Amount by Date

Next you create a line chart to see which month and year had the highest sales amount.

1. From the Fields pane, drag the Sales Amount field from the Sales table to a blank area on the report canvas. By default, Power BI displays a column chart with one column, Sales Amount.

2. Drag the Month field from the Fiscal hierarchy in the Date table and drop it on the column chart.

   

3. In the Fields section of the Visualizations pane, in the X-axis box, remove the Fiscal Year and Fiscal Quarter fields:

   

4. In the Visualizations pane, change the visualization type to Area Chart.

   

5. If you added the DAX measure in the extra credit above, add it to Y-axis as well.

6. Open the Format pane, expand the Lines section, and in Colors, change Sales Amount by Due Date to a more contrasting color, such as red.

   

   As you can see, Sales Amount by Due Date trails slightly behind Sales Amount. This proves that it uses the relationship between the Sales and Date tables that uses DueDateKey.

Visual 3: Order Quantity by Reseller Country-Region

Now we'll create a map to see in which Country or Region the Resellers have the highest Order Quantity Amount.

1. From the Fields pane, drag the Country-Region field from the Reseller table to a blank area on your report canvas. Power BI creates a map.

2. Drag the Order Quantity field from the Sales table and drop it on the map. Make sure Country-Region is in the Location well and Order Quantity in the Bubble size well.

   

Visual 4: Sales Amount by Product Category and Reseller Business type

Next we create a column chart to investigate which products are sold by what type of reseller business.

1. Drag the two charts you've created to be side by side in the top half of the canvas. Save some room on the left side of the canvas.

2. Select a blank area in the lower half of your report canvas.

3. In the Fields pane, select Sales Amount from Sales, Product Category from Product, and Business Type from Reseller.

   Power BI automatically creates a clustered column chart. Change the visualization to a Matrix:

   

4. Drag the Business Type field from Columns, and put it under Category in Rows.

5. With the matrix still selected, in the Filters pane, under Business Type, Select all, then clear the [Not Applicable] box.

   

6. Drag the matrix so it's wide enough to fill the space under the two upper charts.

   

7. In the Format pane for the matrix, search for conditional. In the Cell elements section, turn on Data bars. Select the fx option, and set a lighter color for the positive bar. Select OK.

8. Increase the width of the Sales Amount column so it fills the whole area by dragging the matrix.

   

It looks like Bikes have a higher Sales Amount overall and the Value Added Resellers sell the most, closely followed by Warehouses. For Components, the Warehouses sell more than the Value Added Resellers.

Visual 5: Fiscal calendar slicer

Slicers are a valuable tool for filtering the visuals on a report page to a specific selection. In this case, we can create a slicer to narrow in on performance for each month, quarter, and year.

1. In the Fields pane, select the Fiscal hierarchy from the Date table and drag it to the blank area on the left of the canvas.

2. In the Visualizations pane, choose Slicer.

   

3. In the Fields section of the Visualizations pane, remove Quarter and Date so only Year and Month are left.

   

Now if your manager asks to see data only for a specific month, you can use the slicer to switch between years or specific months in each year.

Extra credit: Format the report

If you want to do some light formatting on this report to add more polish, here are a few easy steps.

Theme

• On the View ribbon, select Themes, and change the theme to Executive.

  

Spruce up the visuals

Make the following changes on the Format tab in the Visualizations pane.

Visual 2, Sales Amount by Date

1. Select Visual 2, Sales Amount by Date.

2. In the Title section, if you didn't add the DAX measure, change Text to "Sales Amount by Order Date".

   If you did add the DAX measure, change Text to "Sales Amount by Order Date / Due Date".

   Make sure you get the Title section for the visual itself, because the axes and the legend can also have titles.

   

3. Set Text size to 16 pt.

4. On the General tab, toggle Shadow to On.

Visual 3, Order Quantity by Reseller Country-Region

1. Select Visual 3, Order Quantity by Reseller Country-Region.
2. In the Map setting section, change Style to Grayscale.
3. On the General tab, in the Title section, change Text to "Order Quantity by Reseller Country-Region".
4. Set Text size to 16 pt.
5. Also on the General tab, toggle Shadow to On.

Visual 4, Sales Amount by Product Category and Reseller Business Type

1. Select Visual 4, Sales Amount by Product Category and Reseller Business Type.
2. On the General tab, in the Title section, change Text to "Sales Amount by Product Category and Reseller Business Type".
3. Set Text size to 16 pt.
4. Also on the General tab, toggle Shadow to On.

Visual 5, Fiscal calendar slicer

1. Select Visual 5, Fiscal calendar slicer.
2. In the Slicer settings section, under Selection, toggle Show "Select all" option to On.
3. In the Slicer header section, set Text size to 16 pt.

Add a background shape for the title

1. On the Insert ribbon, select Shapes > Rectangle.

2. Place it at the top of the page, and stretch it to be the width of the page and height of the title.

3. In the Style pane, in the Border section, change Transparency to 100%.

4. In the Fill section, change Fill color to Theme color 5 #6B91C9 (blue).

5. On the Format ribbon, select Send backward > Send to back.

   

6. Select the text in Visual 1, the title, and change Font color to White.

Finished report

Select FY2019 in the slicer.

In summary, this report answers your manager’s top questions:

• Which day had the most sales in February 2019? February 25, with a sales amount of $253,915.47.

• Which country/region is the company seeing the most success in? In the United States, with an order quantity of 132,748.

• Which product category and reseller business types should the company continue to invest in? The company should continue to invest in the Bikes category and the Value Added Reseller and Warehouse reseller businesses.

Save your report

• On the File menu, select Save.

Publish to the Power BI service to share

To share your report with your manager and colleagues, publish it to the Power BI service. When you share with colleagues that have a Power BI account, they can interact with your report, but can’t save changes.

1. In Power BI Desktop, on the Home ribbon select Publish.

2. You may need to sign in to the Power BI service. If you don't have an account yet, sign up for a free trial.

3. Select a destination such as My workspace in the Power BI service > Select.

4. Select Open 'your-file-name' in Power BI. Your completed report opens in the browser.

5. Select Share at the top of the report to share your report with others.

Related content

• Download the completed Power BI .pbix file
• Learn more about DAX and data modeling in Power BI Desktop

More questions? Try the Power BI Community