KPI report in Power BI Desktop – Dashboard fundamentals

Every modern company needs guidelines, KPIs (Key Performance Indicators) in order to know where it stands against the goals dictated either by itself or by the market. This is nothing new, 100 years ago accountants gave the data to company managers every month, after long work summarizing income and expenses. KPIs were created even then, but they presented the company as a whole. Today, an aspiring, successful company cannot wait months to realize in which area it needs to develop. It is essential that statements and KPI reports appropriate to the given market situation be easily deployable.

By clicking on the link you can jump directly to the quick KPI creation in Power BI Desktop, or you can read further to learn about the alternatives.

KPIs are usually designed for longer-term use: managers need to get used to them and be sure of their accuracy and usefulness. You can crowd a dashboard (a board full of indicators) with KPIs, but if they do not focus on the essence, on areas important to the company, then the managers are only wasting time looking at them.

A metric, a KPI, by its very nature, represents numbers for easier interpretation. One of the basic sources of data is the multitude of Excel files and tables that are created in a company. Excel is great, or if not, it’s definitely a commonly used place to store data, but I don’t recommend creating the KPI report here. It is possible, but not expedient. The expert staff works with the data, the manager with the KPI. To view a graph, it is not efficient to search through the jungle of libraries for the one file that contains both the data AND the graph.

The use of online, dynamic KPI dashboards has a history of several decades in the world, at least in the multinational environment. Lately, this has also infiltrated the lives of small and medium-sized companies, as it makes the life of managers much easier.

I don’t want to start this blog entry with throwing around such trendy buzzwords without an explanation, so let’s go through the terms before we get down to creating the KPI report:

If you have the data that you want to represent, then only the tool shall be selected that will perform the visualization. Some suggestions:

• Grafana: free, web-based graph and dashboard drawing system
• Power BI: the KPI representation program created by Microsoft, fits perfectly into the Excel-Access-Sharepoint ecosystem, has excellent online presentation capabilities

If you have the opportunity, choose the Power BI line. The description below demonstrates the creation of KPI reports using this technique.

The easiest way to create and edit KPI reports is with the Power BI Desktop program that can be installed on Windows. The online Power BI editor is part of the Office 365 package, its functionality is similar, but in my opinion, it is easier to work with a dedicated desktop program than with its online version running in a browser.

The program can be downloaded from any of the following links or directly from the Microsoft Store:

powerbi.microsoft.com

Download Power BI Desktop from Official Microsoft Download Center

After installation, on the welcome screen, we have the option to import a sample database. The description below is based on this data, so let’s download it. It’s a simple Excel file with a table, hidden deep in the directory structure, but if you want to look at it – it’s worth it! -, you can find it around here:

c:\program files\windowsapps\ Microsoft.microsoftpowerbidesktop_2.121.942.0_x64__8wekyb3d8bbwe\ bin\sampledata\ financial sample.xlsx

The initial step in the preparation of every KPI report is the generation of data. This is already given in many cases, e.g. for a specific database, but sometimes we need to gather the data into an Excel file. In any case, it will be possible to access the data later with the other two options on the starter screen.

This step starts the creation of the KPI report. The data source is usually one or more Excel files, but larger data sets can be found in SQL databases. During the import, we specify which of the available data tables we want to use in the KPI report.

When importing from Excel, it is important to pay attention to the icons: if there are structured tables in the Excel file – and unless there is another, very strong reason, then we should use them – then the import screen will display the table itself and also the Excel sheet on which this data is located. In this case, select only the structured table.

The import screen displays sample data. We have the option to call the Power Query editor if changes in the data are needed (filter, data replacement, etc.) before the KPI report is created – the Transform data button opens this editor.

Now we accept the data without changing, let’s load them directly into Power BI.

The user interface surrounds the editing area. Since the structure does not follow the usual Office method, it will not be obvious at first where to find what.

On top of the Power BI Desktop interface are the usual menus and icon bar. The less frequently used functions can be accessed from here, e.g. adding new data to the upcoming KPI report, refining views, applying appearance themes, adding texts and shapes. Most of the work will not be done here, but partly on the visualizations themselves (graphs, tables, filters) and partly on the windows located on the right side.

On the left side of the screen, we can switch between the three main views of the program. The first one is for creating visualizations, it is the most common window. It is empty for now, but will be filled with life later.

The second icon switches to table view. The imported tables can be inspected here. It will be very useful during debugging, since it happens from time to time that the visualization we create does not display the expected result. Don’t forget, those purely display the underlying data in some way. In such cases, it is necessary to check here what the background data holds exactly.

The third icon shows the database model. This will be useful when our data collection consists of several tables. In this case, the connections between the tables can also be built here. It’s an advanced topic, and as such it is outside of the scope of this description.

On the right side of the user interface are the windows that we will use most of the time. Any section can be opened and closed with the double arrow to free up some space.

In the Data window, we see the tables that make up the data model and also their fields. At the moment, we only have one table, the previously imported one called Financials. If it is collapsed, it can be expanded with the small arrow. The Data view is very often needed: the fields shown here will be used to make up the visualizations (graphs, tables) in the KPI dashboard.

The Visualizations window contains the available graphical building units, graph and tables, indicators and filters. We will build our report, the dashboard from these KPIs. Next to the Data view, this window is the core of the report, the content and appearance of the visualizations are set here.

The third window in this section is the Filters. In many cases, it will be necessary to pre-filter part or total of our data  before displaying them in the visualizations of the dashboard. Multi-level, sophisticated filtering can be applied in this window.

At this point, it is necessary to know what data we have (sometimes this is clear, sometimes not obvious), and have an idea on how we want to represent it. A KPI is worth as much as its users understand it. I often see dashboards that are overcrowded, illogical, and difficult to follow. In any case, let’s plan which type of graph or table we want to represent our data on.

To begin with, let’s add a simple bar&line graph to the dashboard. (This is not the simplest visualization, but it is commonly used.) Just press the “Line and stacked column chart” button on the Visualizations window. As it appears, you can drag it to the desired location with the mouse, or resize it by the handes on the sides.

For now, the graph is empty, we need to fill it with data. In contrast to the basic diagram in Excel, we don’t enter specific numbers, but – in the same way as with the Excel Pivot Chart – we drag the fields from the data tables into a specific part of the visualization.

When we click on any visualization (the previously created graph is now selected), the lower part of the Visualizations window changes and shows the subareas of the given graph, table or KPI. These options are different for each visualization, and I guess they require some trial and error before you get used to it. Subtitles help anyway.

Now drag the Sales field from the Financials table to the “Column y-axis” location. Our graph changes, adds the amounts in the Sales column of the background table and represents this in one column. So far so good.

By itself, Power BI will not know what we want to create the bar graph along. It can be a yearly breakdown, year+month breakdown, Country, Product, etc., i.e. all information that are included in any of the fields in the background table. We choose now a simple solution, drag the Date field to the “X-axis” location, thereby specifying the aspect to be plotted on the X-axis.

Not bad, but far from good. Let’s give the graph some space (make it wider). Power BI tries to fill the available space dynamically: although our background data contains daily sales, the graph adds up the data of the months and represents it together.

The Visualization window used now is the most frequently accessed part of the Power BI program, practically all content and appearance settings can be made here. A lot of icons, sliders and input fields have been crammed into this small space.

At the top of the window, we can see which subpage is active:

• Build visual: we define the content of the visualization here, i.e. the specific fields we want to represent and their calculation method, if necessary.
• Format visual: we can change the appearance of the visualization here.

Before moving on to refining the chart format, remove the Quarter and the Day from the Date field. This way our x-axis will be cleaner, the only disadvantage is that we will not be able to “drill down” to the daily level when analyzing the data.

It’s time to adjust the graph to the way we want to see it.

Click on the graph, if it is not active, and in the Visualization window, switch to the Format visual subpage. Even within this submenu, there are two sections (tabs), Visual, where you can adjust the main building blocks of the graph (columns, lines, x and y axis labels, etc.) in detail, and on the General tab you can make settings for the graph as a whole.

The settings are multi-level, if you open e.g. the X-axis section, there the X-axis values (Values) and title (Title) are handled. Although this setup method has improved a lot over the years, it still requires quite a bit of attention when you have several of these sections open to figure out exactly which element of the visualization it’s referring to. It’s worth closing unnecessary parts.

Set the X-axis labels (year and month) to appear together:
Format visual – Visual – X-axis – Values: Concatenate labels ON.

Under this, we can set whether the X axis should have a name (in this case it is clear what we see, but it is necessary at other times):
Format visual – Visual – X-axis – Title ON/OFF, if ON, then Title text: Year – month.

In a similar way, we can turn off the Y axis completely, or rather just its name:
Format visual – Visual – Y-axis – Title: OFF

Add labels to the bar graphs:
Format visual – Visual – Data labels: ON.

The value of monthly sales appears above each column. It would look even better if the number was included in the column:
Format visual – Visual – Data labels – Options: Position: Inside end.

Here we face a problem that, in some cases, the visualization is not large enough to represent the requested data This is indicated by the warning triangle. Either enlarge the graph itself, or reduce the size of the data labels:
Format visual – Visual – Data labels – Values: text size: 8, Value decimal places: 0.
In this way, Power BI can usually display the data captions.

Finally, set the graph header:
Format visual – General – Title – Title: Net sales trend

We can set the color of the text and the background in the same section.

It’s common to prepare the main elements of the graphs, and then fine-tune the operation when the layout is finalized. This can be done simply by clicking on the visualization (a neutral area is preferable so that we don’t filter something accidentally), and then make the changes using the three control windows.

It’s worth noting that we can even change the main type of visualization, e.g. turn a stacked bar chart into a pie chart with one click – but in most cases, this is something that we want to avoid. In case a new graph is needed, first click on a neutral area on the page (canvas), thus de-selecting everything, and only then insert the new visualization.

In the bar chart just created, there is also a line chart option, so let’s use it: click on the background of our Net sales trend chart (this way we don’t filter anything), then drag the Profit field from the Data window – Financials table into the Visualization window:
Visualization – Build visualization – Line Y axis
The amount of profit achieved is displayed as a line diagram each month.

We should turn off the data label figures for this series, because it makes our graph too crowded:
Visualization – Format visualization – Visual – Data labels:
leave the main setting ON, since there is another series where the labels are needed, but turn them off in the Series – Sum of Profit entry: Show data labels: OFF.

It’s important to know that the right-hand windows: Filters, Visualization and Data always behave according to the currently selected visualization. If no table or graph is selected, some general data is shown. This is the state when a new visualization can be inserted – otherwise it will change the type of the currently selected one! In this general state, the Format visual tab switches to Format page, so you can fine-tune the appearance of the page (canvas). Of course, the KPI report or dashboard can have several pages, you can create a new one with the ‘+’ sign at the bottom of the screen, or you can copy the existing one: right-click on the page name, then ‘Duplicate page’.

Let’s prepare the other KPIs in a similar way to the above method, of course each one will represent a different area from the available data. It’s important that when a new visualization is placed on the dashboard, none of the previous ones should be selected, as the program would then change the type of the given graph, not insert a new one.

For a nicer (at least more acceptable) color scheme, I switched the theme to Classroom:
View menu / Themes / Power BI part- Classroom theme

To create our sample dashboard, we use the following visualizations and fill them in with the fields specified below:
Product portfolio – Net sales: Donut chart visualization
Legend: Product field
Values: Sales field

Given discount by product: Funnel visualization
Category: Product, Discount band fields
Values: Discounts fields

In the latter visualization, only the top level, Product, is currently visible. It will be possible to drill down one level during analysing the data, so that the discounts offered to the given Product can be looked up more closely. We will touch this topic later.

Sometimes it is practical not only to see the trend with bold lines or columns, but also to provide the user with exact numbers. During the compilation and testing of the dashboard, it is also essential to double check that our visualization shows accurate figures, and it can be done with temporarily show the data in simple tables. There are two types of the tables, but even the simpler one is perfect for testing and basic presentation purposes.

Let’s create two tables with the following settings:
Data table 1, by country:
Table visualization, Columns: Country, Gross Sales, Discounts, Sales, COGS, Profit fields
Data table 2, by segment:
Table visualization, Columns: Segment, Gross Sales, Discounts, Sales, COGS, Profit fields

The second data table is created simply by clicking on the first one, pressing CTRL+C, then CTLR+V, and replacing the Country field with Segment in the copy. When the field is inserted, it must be placed properly, not after the other fields.

For the benefit of those who are not familiar with finance, here we see the company’s high-level income statement:
Gross sales: total sales, discounts given afterwards have not yet been deducted
Discounts: subsequent price discounts
Sales: actual sales revenue that remains with the company
COGS: all costs of the company (Cost of Goods Sold)
Profit: the amount remaining after deducting costs from sales revenue.

It is important to know that formatting the tables is a rather complicated job, so I recommend that you finalize the first table, and only then do the copying and correction.

I used the following to format the data tables:
Rename fields (columns):
Visualization – Build Visual – Columns: Rename for this visual in the drop-down menu, or double-click on the field name
Setting the number format:
Visualization – Format Visual – Visual – Specific column: in the Apply settings to drop-down menu, select the columns one by one, then set the following in the Values section below:
Display unit: Thousands
Value decimal places: 0

Once you have finished adjusting the data tables, the dashboard will approximately look like this:

If you accidentally clicked on a column or data, they will be highlighted. A click on the same field turns off that filter, or the applied filters can also be deleted manually using the right button + Clear selection on the given visualization.

We are nearing the end of our sample task, but we still need to fine-tune the appearance of the dashboard, and also it is very practical if the user can dynamically filter the visible KPIs. Let’s have a look how we can achieve it.

The KPI dashboard is already functional and shows what we have set up. If we click on any field, the other KPIs are also filtered to that content, so it already offers some limited analysis option. But it is a general expectation that the dashboard report is also equipped with high-level filters that modify all the KPIs with one click for the appropriate time period, product, brand or whatever is needed.

For this purpose, the Slicer visualization is most often used due to its versatile and customizable nature. We will use four separate Slicers on the dashboard to filter the following areas: Year, Month, Country, Product.

Click on an empty area and create a Slicer visualization. Drag the Country field into the Field section. From here on, if you select any country – or several by holding down the CTRL key – all other KPIs will be narrowed down to that country, only its data will be displayed.

The primary appearance of the Slicer can be set in the Format visual – Visual – Slicer settings – Options section. If it is a text field, we can choose from a checkbox list, a drop-down menu, or a tiled solution. In the case of number and date type fields, additional options are opened, which are tailored to the specific type.

Let’s change the Country Slicer to the Tile type, and then we can reposition it to a better fit on the page.

Adjust the remaining three Slicers: the selector for the Year field should also be of the Tile type, while the Month field of the Between type – this is the default setting. Product should remain Vertical list. Drag the size of the Slicers to the appropriate size by their side handles or corners.

Since the Slicers are probably on top of each other, and a full header is required at the top of the dashboard anyway, let’s get things organized by sizing and dragging the KPIs into place. Let’s leave some free space at the top of the dashboard.

Static texts and general control buttons (e.g. for navigation between report pages) are not in the Visualization window, as they do not interact with the user’s actions. You can find them on the right side of the Insert main menu.

Select a Text box and enter the text: Sales per product and selling channel. The text box format settings can be found in the Visualization window on the right, the most useful options (background color, shadow) are in the Effects section. You can change the settings of the text itself (font size, italic/bold, etc.) on the text box.

Even in its current state, the Power BI dashboard is excellent for analyzing KPIs: we filter on any element, all the other visualizations will react, narrowing down its data to the topic we are focusing on.

If several fields from the data are dragged into one section of a visualization, by default only the top level is displayed, but with the help of the icons above the graph, one can go down in the hierarchy in various ways, so the deeper reasons can be examined. I guess you need to get used to how the drill-down buttons work so it’s worth practicing for a few minutes.

The completed Power BI dashboard is stored in a file with the pbix extension. In this case, pbix includes the visualizations, i.e. the structure of the dashboard, as well as the data, so the source data is no longer necessary for its operation – only if it changes and we want to update the KPIs from there. This is what the Refresh button in the Home menu is for.

If you do not have a Power BI online subscription or the license is not at a sufficiently high level, you can also share the pbix file itself or put it on a common drive, so users can access it. The significant disadvantage of this is that the Power BI Desktop program must be installed on every computer, and users can practically use the report in editing mode – it is very easy to get confused by a misplaced clicking. We can compensate for this somewhat by turning on the Lock objects option in the View menu.

This can be done with the appropriate Microsoft license via the Publish button in the Home menu. From here on, users can access the report conveniently, even on the go, from a phone or tablet via the browser, without opening a file.

I hope you found this brief introduction to the world of Power BI useful. If you’d like to know more, I’m happy to offer a training about the additional possibilities of Power BI – this description only scratched the surface. If you want a ready-made solution for your company, I can help with that too. By filling out the contact form below, I can give you a personalized offer regarding the development of Power BI dashboards.