How to Build Power BI Reports from Start to Finish

Summary

The video demonstrates how to build two interactive Power BI report pages, analyze budget variances, and publish and refresh reports using Power BI Desktop and the online Power BI service.

1. Power BI workflow and sample data

Power BI Desktop is used to connect to data, transform and model it, and create reports. Reports can then be published to the Power BI service for sharing and collaboration. Power Query is used to import and clean data, while the model and measures use Power Pivot concepts and DAX.

The example dataset covers IT department costs for a global company and includes:

• 12 months of budget and forecast data.
• 6 months of actuals.
• Dates, departments, cost elements, countries, and amounts.

Because the data represents costs, actual or forecast costs above budget are treated as adverse variances.

2. Import and prepare the data

• Import the cost-element, country, and department dimension tables from an Excel file.
• Import budget and forecast data from CSV files.
• Combine monthly actuals files into one table using Get Data → Folder → Combine and Transform.
• In Power Query, remove unneeded columns and filter out data that will not be used, then load the prepared data into the model.
• Set numeric formatting, such as comma separators. Assign country fields the appropriate geographic data category so Power BI can use them in map visuals.

3. Build the data model

Arrange fact tables below dimension tables in Model view and check that relationships filter from dimensions to facts. Review automatically created relationships and add missing ones where needed. In the example, department relationships for budget and forecast must be added manually because the column names differ.

Create a calendar dimension and relate its date field to the date fields in all three fact tables:

• CALENDARAUTO generates dates based on the model’s date range and can account for a fiscal-year start month.
• Use CALENDAR when you need to specify exact start and end dates, particularly if the model contains dates outside the reporting period.

4. Create measures and the headline report

• Add a gauge comparing actual year-to-date with the full-year budget.
• Create a KPI using forecast as the indicator, budget as the target, and dates for the trend. Since higher costs are unfavorable, configure the KPI so decreasing values are positive and low values are good.
• Add a line chart for actual, budget, and forecast by month.
• Write DAX running-total measures for actuals, budget, and forecast, then display them in an area chart.
• Use Quick Measures to create:
  • Budget minus forecast.
  • The percentage difference between budget and forecast.
• Build a combo chart with the budget-versus-forecast amount as columns and its percentage difference as a line.
• Create clustered bar charts for budget-versus-forecast percentage by region, cost-element group, and IT area. Apply conditional formatting so negative percentages appear red and positive ones gray.
• Add a region dropdown slicer and a card showing the year-to-date reporting period.
• Apply a report color theme and adjust visual titles and colors for readability.

5. Explore changes and build the regions report

Use cross-filtering and highlighting to explore how selections in one visual affect others.

The presenter demonstrates the Explain increase/decrease analysis tool, which uses charts to break down changes by factors such as cost element or department. The video notes that this feature is available to Power BI Pro and Premium users and may change while in preview.

On the regions page, add:

• A clustered bar chart comparing actuals and budget by region.
• Matrix tables showing budget-versus-forecast results by IT area and cost-element group, with red and green icons for negative and positive variances.
• A filled map showing actual costs by country, using a color scale to indicate magnitude.
• A scatter chart with budget-versus-forecast amount and percentage on the axes, country in the legend, and forecast as bubble size.

6. Use a decomposition tree

Add a decomposition tree to investigate the factors contributing to an adverse budget variance. Expand the measure by dimensions such as cost-element group, cost element, country, and department.

Use the tree’s AI suggestions to identify low-value contributors, or select dimensions and drill-down order manually. The example shows how the tree can reveal different contributors to unfavorable and favorable variances.

7. Publish, share, and refresh

Save the Power BI Desktop file and publish it to a workspace in the Power BI service. In the service, users can interact with report visuals, use Focus mode, filter data, and view a visual’s underlying values as a table.

Pin a live report page or visual to a dashboard. A live pin reflects later report changes; dashboards offer a streamlined view and include a Q&A feature.

The presenter describes sharing reports with selected users through the service, where recipients sign in to their own Power BI accounts. Public embedding is another option for non-confidential data.

To refresh data stored on a local computer:

1. Configure a personal gateway.
2. Add new files to the source folder.
3. Refresh the dataset in the service, then refresh the report.

Scheduled refreshes can also be configured.

The video cautions that published reports and datasets reside in the Power BI service. It also notes that sharing generally requires Pro licensing and that row-level security is needed when users should see only data assigned to their roles or regions. Licensing details may change over time.

Speakers and sources featured

• Speaker: The video’s presenter from MyOnlineTrainingHub. The subtitles do not identify the presenter by name.
• Data source: A modified budget-and-actual dataset attributed in the subtitles to “Obvian,” with budget and forecast CSV files, monthly actuals files, and Excel dimension tables. The subtitles describe the CSVs as exports from an accounting system.
• Tools and platforms: Power BI Desktop, Power Query, Power Pivot/DAX, and the Power BI service.