How to Create Reports with Power BI Service

Power BI, a business intelligence solution from Microsoft, enables end-users to create insightful reports and dashboards with little effort.

Free webinar: Enhancing SharePoint Cross-Project Reporting with Power BI Dashboards

Using Power BI, anyone in your organization can uncover actionable, hidden insights in any data source.

In this article, we’ll take a closer look at creating reports and dashboards with Power BI Service, a cloud-based solution. You’ll see how to import data, create and edit reports, and build dashboards.

Power BI service is aimed at users, rather than report designers. However, it’s a very useful way to build reports and dashboards for project reporting.

We’ll also take a brief tour of Power BI Desktop, the primary authoring tool for Power BI reports.

A full exploration of Power BI Desktop is beyond the scope of this article! As such, you’ll find additional training resources and videos listed at the end of the article if you’d like to learn more.

The examples in this article are based on Microsoft samples and project reports in the BrightWork Power BI Pack.

Key Elements of Power BI Reporting

Microsoft Power BI is a ‘’collection of software services, apps, and connectors that work together to turn your unrelated sources of data into coherent, visually immersive, and interactive insights.’’

BrightWork Power BI Pack for SharePoint

Before creating your first Power BI report, it’s helpful to understand the services you’ll use to import and transform data and create visualizations.

Certain services are limited to Desktop and are noted as such below.

Power Query — Desktop

Power Query is the engine for importing data and transforming (cleaning) data.

Data is imported through a simple interface and transformed using the ‘Power Query’ editor before loading into the report area.

Options include:

• Add or remove rows and columns
• Separate rows or columns
• Remove null values
• Remove rows with blank values.

Changes applied in the Power Query editor do not impact the underlying dataset.

Power Pivot — Desktop

Power Pivot is used for creating data models, the relationships between tables, and performing calculations.

Calculations use DAX, Data Analysis Expressions, to interrogate data. DAX statements are similar to Excel functions.

Power BI includes over 600 DAX functions such as sum, average, max, and count. Users can also create custom functions.

Power View — Desktop and Service

Power View delivers data visualization in various Microsoft applications, including Power BI, SharePoint, and Excel.

Visualizations are used in reports and dashboards in Power BI to help users to understand complex information quickly.

Think about your data and your query before selecting the right visualization. As a brief guide:

• Area Charts are useful for comparing two or more quantities and tracking trends over time.
• Bar and Column Charts are used to compare a value against a particular category.
• Pie Charts present data as a percentage, adding up to 100%.
• Funnels depict the flow of data to an end-point.

Additional Power BI visualizations include line charts, maps, tables, and KPIs.

Power BI Reports — Desktop and Service

A Power BI report refers to a collection of visuals and can span multiple pages.

Reports connect to one dataset, or data model, only. Power BI can combine multiple data sources into a single report.

Unlike an Excel spreadsheet, Power BI reports are interactive, with options to filter, slice, and drill-down.

Automatic refresh updates all visuals in a report based on a fixed interval, for example, every hour or based on changes in the data.

Power BI Dashboards — Service

A Power BI dashboard is a one-page summary of your data, with visualizations or tiles.

Each tile links to an underlying report, making it easy to drill-down for more detail as needed.

A dashboard can contain links to multiple reports, providing a quick overview of your data in one place.

As you’ll see below, the ability to create dashboards is available in Power BI Service only.

Power BI Reports: Desktop v Service

At this point, you may be wondering if you should build your project reports in Power BI Desktop or Power BI Service?

The simple answer is Power BI Desktop! Using this application to import and transform data, create models, and generate rich reports will generate new insights about your projects.

Power BI Service does not support data editing or modeling.

However, Power BI Desktop does require practice and knowledge of data modeling. It’s essential to use the Query Editor and Model tool correctly before creating any visualizations.

If you are using a clean dataset and need to create reports and dashboards to share reports with your team, Power BI Service is the right tool.

Below is a brief comparison between Power BI Desktop and Service.

Power BI Reports: Desktop v Service

DesktopServiceCostFreeA free license to create reports.
A paid license to share reports.Data SourcesImport data from a wide range of sources.Import data from a wide range of sources.Data SourcesOne data set per reportOne or more reports and data sets per dashboard.Data editingTransform (clean) data for usage.Use original data.AnalysisAnalyze data using models and DAX functions.Generated by Power BIVisualizationsBuild and edit visualizationsBuild and edit visualizationsDashboardsNoCreate and shareWorkspaces NoYesCommentingNoYesSharingPublish to Power BI Service to share.Can share with internal/external users.

In the next section, you’ll see how to create a report and dashboard in Power BI Service.

The example is based on the free Supplier Quality Analysis sample from Microsoft.​

How to Create and Format a Report with Power BI Service

Start by getting your data. There are a number of ways to import data from within your organization, file directories, and databases. In this instance, we’ll select Samples and import a pre-configured dataset. The data is imported as-is with no editing options.

The dataset will appear in your workspace in Power BI Service. An easy way to start analyzing the data is to select ‘Get Quick Insights’ from the menu. A dashboard is generated whilst you work on the data.

Next, select ‘Create Report’ to open the report builder. There are three elements in this area:

• Canvas. This area is blank until visuals are added
• Visualizations pane: Use to add and edit charts.
• Fields pane. A list of the fields in your dataset, based on the columns in your dataset.

To start building a report, you can either select the relevant fields from your data or pick a visualization. The visualization is displayed on the blank canvas.

To add a field to your report, simply click the relevant field. Power BI will automatically add the field to the right area in your chart. You can also drag and drop fields into the axis, legend, and values area.

As you add fields, the chart will start to take shape. If needed, change the chart by selecting another option in the Visualizations pane.

Once you are happy with the data visualization, it’s time to format the chart. Click the ‘format’ icon to access a range of options, including the size and colors of the chart, borders, and tooltips. If you’d like to add a title or details to the report, click the ‘Text box’ option in the top menu.

Use the ‘Reading View’ to check how the report will appear to others.

Add more visualizations as needed.

Remember to save your work.

Next, create a dashboard by selecting ‘Pin to a live page’. You can add the report to an existing dashboard or build a new dashboard.

Dashboards offer numerous editing options, including the ability to add more tiles and different content types.

When you’ve finished your work, there are a few ways to use your reports under ‘File:

• Export reports to PowerPoint or as PDF
• Embed in a SharePoint site.

At any time, personalize your display settings using ‘View’. Change the size of the report and colors for improved readability.

That’s it — you’ve created your first report and dashboard in Power BI Services.

To share your report, select ‘Share’ from the top navigation, complete the form, and share it with your team. Reports and dashboards may be shared with internal and external users.

There are a few caveats to note about report sharing:

• Recipients can view and interact with the report or dashboard but can’t edit it.
• Depending on permissions, recipients can share the reports with others.
• You will need a Power BI Pro license to share your reports. Recipients will also need a license to view the content.

Further details on sharing, including permissions and limitations, are available from Microsoft.

BrightWork Power BI Pack

The BrightWork Power BI pack for SharePoint is a pre-built report with six dashboards.

The pack connects to the three BrightWork project data sources — Project Cache, Work Cache, and Project Status Report Cache — for complete insight into projects and portfolios.

Aimed at senior executives and project managers, the Power BI pack includes:

• Portfolio dashboards with a roll-up summary of all project sites. The dashboard offers four filtering options: “Project Status,” “By Project Manager,” “By Project type,” “By Department,” and “By Priority.
• Portfolio timeline, a Gantt view of all project sites. Drill down by year, quarter, month, week, and day.
• Project and Task Timeline, a Gantt view of projects and tasks. Filter by project name, project type, project manager, and task owner.
• Work Reports allow project managers to check the status of tasks, risks, issues, and goals.
• The Status Report Dashboard includes RAG indicators, weekly updates, highlights, lowlights, and next steps.
• The Issue Report helps project managers to get further information on issues, including the task-owner.

The BrightWork Power BI Pack is available to BrightWork customers, with additional services delivered by our Customer Success Team.

To learn more about these services and reaching your project management goals with BrightWork, please contact Customer Success (existing customers) or our Sales team.

Power BI Desktop: Resources and Videos

• Desktop to Dashboard in 60 Minutes (Avi Singh, Microsoft MVP)
• Microsoft Power BI Samples
• Power BI Documentation
• Clean, transform, and load data in Power BI (Free Microsoft Training Module)