Design a data model in Power BI

Review the model

  1. In the Data pane, expand all tables to reveal all fields.

  2. In the Sales table, point out the OrderDate hierarchy, which was created automatically.

  3. Explain that a Date table will be created in the next demo.

  4. In Model view, hover over the automatically-created relationship between the two tables.

  5. Explain how filters will propagate from the Product table to the Sales table.

Create a hierarchy

  1. Create a hierarchy based on the Product table Category column.

  2. Rename the hierarchy as Products.

  3. Add the Product column as the second level.

Set model properties

  1. Hide both the ProductID columns.

  2. Format the Quantity column to use the thousands separator.

  3. Multi-select the Sales and Unit Price columns, and format them to use two decimal places.

Validate the model design with a matrix visual

  1. In Report view, add a matrix visual to the page, and then size it to fill the entire page.

  2. Add the Products hierarchy to the rows, and then add the Quantity, Sales, and Unit Price fields.

  3. Expand all levels of the Products hierarchy.

  4. Point out that the Unit Price values are the sum of prices, which is not correct.

  5. In the Data pane, select the Unit Price field, and configure its summarization to use Average.

  6. Remove the Sum of Unit Price column from the matrix visual, and then add the Unit Price field back again.

  7. Save the Power BI Desktop file.

  8. Leave the Power BI Desktop file open for the next demo.