Dashboards in Power BI: stages of creation and development

There are numerous data visualisation and analysis tools available. Some require no particular user training but have limited capabilities, while others demand expert data analysis and programming skills. Business Intelligence (BI) platforms built for business analytics are the most current and powerful technologies in this category. We'll take a look at one of these platforms and show you how to make dashboards in Power BI.

The platform on which we will be working is made up of 5 sections:

1. Power Query - for connecting to data sources. In this section, we will build "Calculated Columns" and "Measures" (DAX formulas that assist in calculating values for any indicator).
2. Power Pivot - for creating a data model. We establish relationships between columns of separate tables and create the interactivity of the future dashboard.
3. Power View - for drawing graphs and charts. We put data from tables and generated measurements on them here.
4. Power BI Service - provides users with access to dashboards from their personal computers.
5. Power BI Mobile - allows viewing created work from mobile devices.

This tool has been considered one of the best in the world for many years. It is suitable for building interactive panels with business information, results of sociological surveys, polls, and any types of data that can be organized into a specific structure. To get started, you need to download the program, register, make sure you have material to visualize and analyze, and then you can start working.

This is a visual representation of a report for the consumer (a specialist or a management) in the form of interactive graphs, charts, and tables on a single page.

"Dashboard" literally means "instrument panel," but in business analytics, it refers to an interactive report with visualisation. It connects to live data sources and displays indicators in real time.

The comprehensive interrelationship of visual elements allows users to perceive the overall picture quickly and conveniently without switching attention to different tabs, files, transitions, and so on. In a nutshell, this technology displays information from all tiers in a single window.

The data on the panel is filtered by clicking on a certain column or row with the mouse. This enables the user to acquire all pertinent information about the specified indicator. We'll go through this in more detail with an example.

Creating dashboards with Power BI for business allows you to objectively assess performance metrics and the effectiveness of all activities. New decisions, tactics, and strategies can be established based on these assessments.

Representatives from numerous positions and expertise use these informational panels in their daily work, such as:

• Managers at all levels;
• Marketers;
• HR managers;
• Sales managers;
• Accountants, and more.

Interactive dashboards have a wide range of applications. They are utilised in the media, information and analysis agencies, government agencies, and a variety of corporate sectors. A film might provide a brief summary of how such an analytical panel operates and what it signifies for a building company.

To describe how to create dashboards in Power BI, you will need the data in tabular form as well as the programme in which you will execute the visualisation.

The Power BI Service component, which is not present in the Power BI Desktop application, is responsible for this. It's essentially the same dashboard—when a user clicks on a link to a prepared report, their browser is sent to a page on the cloud server known as the monitoring panel.

Individual dashboard charts and features are referred to as tiles. They are displayed exactly as the creator intended in the format and arrangement. This presentation does not allow for changes or adjustments to the content of the panel, but it does provide a full view of the facts in the report.

Creating dashboards in Power BI provides the ability to filter by any parameter (if implemented by the developer). For example, in our case, when selecting a specific brand in the tile on the left, we receive data on all the charts specific to that brand.

If we click on the column "Average check, M$" in the "Penthouse" tile, we will receive data only for this segment.

In the same way, you can filter data by months, sales, and other parameters analyzed in the report.

As a result, the monitoring panel is a useful interactive tool that gives users a thorough insight of the present company environment and emerging trends.

On the left side of the screen, there is a navigation section. It follows the general principle of Microsoft's software interfaces. You can specify target indicators in the "Goals" tab and correlate them with specific visualisations in the report. You can add monitoring panels to Favourites (inside Goals) and create new dashboards (albeit doing so in Power BI Desktop is more convenient and preferable).

Each individual chart or table on the dashboard, or visualisation, is referred to as a tile. Power BI Desktop has a standard set of elements like as diagrams, charts, their combinations, tables, slicers, funnels, and others.

The programme displays the information we chose from the columns or measures of the original data in the form of a chart or table in each tile.

The dashboard's developer can add extra visualisations from files as well as the free Power BI repository, which contains a wide collection of developments from other sources.

So, let's start creating our project. At the core of any report are the data. Power BI allows you to extract them from various sources, such as SQL servers, different databases, individual files, and so on.

As we can see, this programme can handle and deal with nearly any information source. We will use Excel tables with data on automobile chemistry sales in several cities in 2021.

In this example, you will learn how to use Power BI to develop a sales dashboard. Even if you've never worked with vehicle chemistry or sold things in Kazan, the directions will be easy to follow. Data visualisation approaches and algorithms are universal and user-friendly.

The first thing to consider is how the information on the dashboard will be organised. To do this, the structure and substance of the final output must be planned ahead of time. The video includes a brief tutorial on how to properly arrange data and build an efficient layout.

We need to halt and perhaps take a step back at this point. Power BI, like any other product, can only work with a specific form of data organisation. As a result, we must ensure that our file is formatted in a way that the system can understand.

Excel files are broadly classified into two types: reports and sources. Reports are well-formatted tables that are acceptable for human analysis but not for further system processing. Sources are structured tables that can be analysed by machines.

Here's an example of a report-style table:

The figures for autochemistry sales in 3 regions for 2021 are provided here. Because all signs are clearly visible, this is a useful and visually appealing item for a manager or leader. This format, however, is unsuitable for machine analysis because it cannot be further processed.

Our objective is to convert this data into a source table (also known as a "flat" table). Power BI's guiding idea is columnar data analysis. In other words, unlike Excel, the programme works with full columns rather than individual cells.

A source table's fundamental feature is that each column represents a distinct type of information. Cell merging is not permitted in the header, which should be written in a single row with non-repeating titles. Furthermore, grand totals (summing) are unnecessary on a flat table because the system will calculate everything during analysis. It is not permitted to have completely empty columns or rows.

To construct a dashboard in Power BI, just reformat any "nice-looking" report table into the needed flat format. There are various ways to accomplish this, ranging from manual formatting to editing in Power Query. We won't go into the methods here, but the essential point is that after some data modifications, we should end up with something like this:

These are the same figures as in the previous image, but they are now shown in the so-called flat format. We obtained four distinct columns, each carrying only one type of data. We can upload this table to the platform, which is exactly what is required when producing dashboards in Power BI.

To begin, launch the Power BI Desktop programme, which is where all report design work is done. The following window appears when you launch the programme:

Select "Get Data," then "Excel Workbook" from the top left menu. Then, using the familiar file manager, upload the necessary file.

The Navigator will open with a glimpse of the data after a few seconds.

First, select the sheets from the file that you require (in this case, one, but there can be as many as needed), then click "Load." You'll be directed to Power BI Desktop's primary working sheet, where we'll build our dashboard.

The "Home" page allows you to interact with the data source, including changing it from within the programme using the "Transform data" option. The "Refresh" button is used to update all changes made to the data source outside of the programme. You'll also see the "Publish" button here, which will come in handy later on when the dashboard is ready to be shared with other users.

The "Modelling" page has tools for adding new tables, columns, and derived measures. The "View" tab is used to change the dashboard's general visual appearance.

On the left side of Power BI Desktop, there is also an option labelled "View Modes." It has three icons, which we will describe in order (from top to bottom).

1. Reports - In this mode, all visualizations are created, and the final dashboard is generated.
2. Data - This is a tabular view of the data where you can perform additional calculations and adjustments.
3. Relationships - Here, you configure the relationships between different tables.

For creating and editing charts and diagrams, there are separate panels on the right.

To begin, it's important to understand how and which data you want to display, meaning you need to determine the list of tiles on the dashboard. The next step is to create a preliminary layout of the future report either mentally or on paper. There are general recommendations for this, the essence of which boils down to a fundamental and obvious rule: the information on the interactive panel should be easy to read and have a logical structure.

To make our Microsoft Power BI display more aesthetically appealing, we added a column called "Profitability" to the car chemistry sales data. We opted to display the following information in the charts and diagrams: a quarterly report on revenue and profit, sales by product, sales by city, and the city's percentage of overall profit.

When choosing reports, keep in mind that in Power BI, each tile can function as a slicer. This means that selecting a parameter will filter the data on all of the dashboard's charts.

Let's get started on our report. We select the required visualisation, and an empty tile appears for us to fill in.

We use a barchart with grouping as the visualization to illustrate sales and profit by quarter. To make the tile chart, click the "Fields" icon in the visualisation pane and select the data from the right panel. Drag the "Period" field to the "Axis" section and enter the "Revenue" and "Profit" values into the "Values" section. This will result in the creation of the histogram on the tile.

Now you need to format the visualization. Select "Format" (the paint roller icon) in the Visualisation window and edit all the options.

We changed the "Title" section, switched off the Y-axis, and removed the X-axis title in our example. We enabled data labels and formatted their size and placement. It is advised to do so whenever possible in order to reduce duplicate information and simplify perception. You can also adjust the colours, apply conditional formatting, work with borders and tile backgrounds, and much more in this part. The main goal of this stage is to refine the visualisation by removing any extraneous features. As a consequence, you'll have a chart that is concise and easy to understand.

Following the same algorithm, you create the remaining visualizations. Key indicators are recommended to be presented as cards (standard type) at the top of our panel.

It is impossible to include all of the system's analysis capabilities in a single article. It is critical to remember that the more complicated and detailed the report you wish to make, the more features and options will be required. And, of course, they are available!

During the process, for example, you may require more computed measurements. Profitability has to be calculated in percentages in our scenario. To do so, select "Create Measure" from the main menu's "Modelling" tab and enter the expression for this measure in the DAX language. This is a significantly higher level, but it is reachable with the correct drive and effort.

The dashboard for analysing automotive chemistry sales that results from the processes outlined looks like this: [picture or description of the completed dashboard].

Please keep in mind that while developing full dashboards with advanced capabilities may necessitate a solid understanding of the Power BI tool, data modelling, and the DAX language, the software does provide the tools to produce sophisticated visualisations and data analysis.

There were only 2 columns of actual data in our original file, but even this small amount allows you to produce an insightful and visually beautiful report.

When you have successfully created a dashboard in Power BI, you often need to show it to the end user. As mentioned earlier, publishing is done in the cloud service. In the "Home" tab of the tool menu, click on the "Publish" button on the right.

A window appears where you need to choose the target folder named "My Workspace." Click "OK," and the following window appears (the publishing process may take some time):

You need to click on the link "Open 'Report Name' in Power BI" and authenticate yourself. Upon successfully entering your login and password, the monitoring panel will open in your browser, displaying the dashboard you created.

But currently, it's only visible to you. Now, select File → Embed report → Publish to web.

Next, agree to produce an embed code for a public website and press the "Publish" button. Don't worry, your work will not be made public; instead, you will receive a URL that you can distribute as you see appropriate. Finally, you should see something like this:

That's it! Copy the link and send it to everyone who really needs it!

As seen from our instructions, building dashboards in Power BI is quite feasible even for beginners. However, of course, the capabilities of the platform are much broader than what was shown in the example.

It is essentially a sophisticated data visualisation application that combines the functions of Excel and PowerPoint. You can install it on your computer for free and use practically all of its functions indefinitely (extra paid alternatives are available, but they are more important for larger firms or more complex multifactor research).

Thus, the platform has 3 major advantages over other options:

- Power BI can be used for free.
- It is intuitive for anyone who has worked with Excel and PowerPoint.
- Designed for visualization, so many functions are automated (unlike Excel, where additional steps are required).

Many of the tasks for which the platform is designed can be solved using other ways, such as Excel using pivot tables and slicers. Power BI, on the other hand, completes these tasks significantly faster and with higher visual clarity.

• It consists of several modules, each contributing to the creation of a dashboard.
• Unlike Excel, it performs calculations not on individual cells, but on columns. This fundamentally changes the style of working with data.
• The programming language used is DAX, which significantly differs from Excel formulas in terms of syntax and logic.

If you recognise the potential of this programme and wish to use them in your job, the information in this article is insufficient. Creating Power BI dashboards demands greater practise and knowledge of analytics.

People frequently report feeling "stuck" during their first encounter with this programme. Everything is significantly different from the conventional Excel. "Do we really need this?" one wonders. We have Excel, and it has always sufficed!"

The explanation is simple: BI platforms are built to perform in modern conditions, when business processes are quickening and the volume of information available for decision-making is increasing.
We illustrated the primary stages of constructing a dashboard with Power BI and covered its key capabilities in this article. It turns out that it's not as difficult as it appears at first.