An Interactive Dashboard in PowerBI
There are various data visualization styles and tools available in the market. Each tool comes with its own pros and cons. Recently, I had a chance to teach a class of 50+ about PowerBI, and I wanted to share what I have learnt from that experience here. I have learnt multiple methods to develop dashboards in PowerBI. Out of that, I will show you one of the interactive dashboards using PowerBI.
So let us get started with this Power BI dashboard article in the following sequence:
What Is Power BI?
Reports In Power BI
Power BI Dashboard
Dashboards Vs. Reports
Creating Dashboard in Power BI
What Is Power BI?
Power BI is a business analytics service provided by Microsoft. It provides interactive visualizations with self-service BI capabilities. End users can create reports and dashboards by themselves. This means they don’t have to depend on information technology staff or database administrators.
Power BI also gives you cloud-based BI services, known as “Power BI Services”, along with a desktop-based interface called “Power BI desktop”. It offers data warehouse capabilities, including data preparation, data discovery and interactive dashboards. In March 2016, Microsoft released an additional service called Power BI Embedded on its Azure cloud platform. Using it, one can deliver reports, analyze data easily and perform various ETL operations with Power BI.
Power BI gateways let you connect SQL Server databases, Analytical Services, and many other data sources to your dashboards. Reporting portals embedded Power BI reports and dashboards to give you a unified experience. All this while I have used the terms ‘reports’ and ‘dashboards’ quite a few times. Let us try to understand these terms one by one in the next section of this Power BI dashboard blog.
Reports in Power BI
A Power BI report is a multi-perspective view of a dataset, with visualizations representing different findings and insights from that dataset. A report can have a single visualization or pages full of visualizations. The visualizations in a report represent something like a dashboard does but serve a different purpose.
The visualizations in a report represent a nugget of information. These visualizations aren’t static, and you have an option to add and remove data, change visualization types, and apply filters in your quest to discover insights and look for answers. Like a dashboard, a report is highly interactive, highly customizable and the visualizations update as the underlying data changes.
The image below represents how a sample report looks.
Power BI Dashboard
The dashboard is an essential distinguishing feature of Power BI service, which monitors your most valuable data immediately. A dashboard combines on-premises and cloud-born data. It provides a consolidated view regardless of where the data lives. There are several ways to create a dashboard. Here’s a step-by-step guide on how to create a power BI dashboard!
A dashboard is more than a pretty picture that is highly interactive and customizable. There are so many ways to work and interact with a dashboard that one article cannot cover them all.
People often confuse dashboards with reports since these are also canvases filled with visualizations. But there are some significant differences. Let us look at these differences with the help of the following.
Dashboards vs. Reports
The key differences between these two are as follows:
Information Level: Reports are created on multiple pages, so every detailed analysis and information is available with “Reports.” We drill through the reports. Dashboards include only valuable information in the large data set, which is critical for quick decision-making.
Interactivity: Reports are embedded with slicers and filters, so if the summary table shows only monthly sales, then by adding the category field to the slicers, we can select each category individually and see how each category performs across months. Dashboards may not have this interactivity. We may see monthly and category-wise sales values in different tables or visuals. Users need to look at two different tables and find the differences.
How to Create a Dashboard/Report on Power BI?
Now there isn’t much use for the theory of Power BI if you’re not going to create reports on your own. Depending on your role, you may be someone who creates reports for your own use or to share with colleagues. Either way, you want to learn how to create and share reports or at least understand how to interact with reports.
The image below shows how the Power BI desktop interface looks like. We have three tabs on the left-hand corner of the interface. The first tab is the report tab, which is visible by default, and we are going to use it to create reports. Next is the data tab which is used to see the imported data sets. The last tab is the relations tab which gives you the relationship between different variables in a data set, if they are well defined.
You can easily import data sets into Power BI. This can be done by clicking on the Get Data tab. We have the Visualizations tab on the right-hand corner of the screen. All the various kinds of visualizations that are listed under it can be used to meet your requirements. There is a Fields tab next to the Visualizations tab, which gives you all the fields that your data set has.
Now I am using the data set that PowerBI offers for practice purposes, so I'm using the same. When you open the PowerBI Desktop app, you will get an option to try a sample dataset, or you can go to HELP, and under that, you will find an example under that you will find a sample dataset. Once You click the dataset, you will get the image below.
Once you click Load sample data, the interface will allow you to load or edit the data as it is visible in the image below.
So, this screen will open so this screen will tell you that in your data source how many tables are there, so we have this financial table in our data source in the back end, so if we click on this table, we have three options here so cancel obviously we won’t be cancelling this so two options are load and transform data so if we click on load this data set would directly load inside of PowerBI but ideally, we should always go for transform data because if we need to change a data type rename columns or remove columns or change the name of the table or any transformations fitting columns or delimiting rows un dividing columns for any kind of changes, we need to transform data first and we also can understand our data here, like what columns we have and their values and all. And then when you click on load option, your data will be loaded into your PowerBI screen. You will get all the fields on right side under field view as this following image.
With Power BI, all you must do is select the visualizations and drag the required fields and drop them on the visualization template to view them. The most important thing is whenever you’re creating a dashboard, you should always look from left to right, not right to left the audience or the viewers, or your stakeholders wherever you are working even if you’re making an excel dashboard, always try to start your dashboard from the left-hand side your left-hand side should cover all your filters, and on the top, you can have your KPI's like sales profit cost discounts and below that you can have your charts and do not keep more than six charts in one screen then it looks clustered and or the visual appeal kind of goes away right. So, let’s start with the KPI's first. Please refer to the image below. I selected the card type under the visualization pane and then select the sales value. By default, PowerBI shows the sum of sales.
As you can see from the above image, I wanted to show the values of sales, profit, cogs, and unit sold. You can copy-paste one card, and you need to change the value.
As you can see from the above image, we can go to the format option and align KPI's with distribute horizontally and filters with the distribute vertically so that it distributes evenly.
In the above image, you can see we can choose our filters however we want them to look like. If you click the 3 dots you will get 2 options List and Dropdown.
On the right-hand side, you can see the format page. That page where we are working is called canvas. You can see a lot of options like canvas settings and canvas background. From the background, I gave the color black.
So, for each visual, you can format it from the format pane. Just click on that visual and try changing it however you want.
After that, I wanted to show you how you can format with a format painter. As you can see from the above picture, there is an option format painter under the home tab. So, let’s say you have done all the formatting in one visualization, and you want the same for another one. Then you need to click on format painter and take your brush to that visual and click it.
For this dashboard, I have used a card, slicer, clustered bar chart, scatter chart and ribbon chart. And you can see the reset button on the left side. From the below image, you can see the buttons option. You will find it under the insert pane next to the home pane. And click on the reset. So, resetting is nothing, but if you change your filters to see the data for one product or country. So you don’t need to do it manually and uncheck it. You can go to that reset arrow button and CTRL + click. It will reset how it looks in the above image.
After you get that reset button you need to set it up. So, for that, you need to go to the view pane and under that you will find bookmarks. Just add the reset button in your bookmarks.
After adding a bookmark, you can go to format under that, just set your action type as bookmark and bookmark as reset.
And lastly, I want to show you how drill-through works in PowerBI. As you can see from the below image, there is an add drill-through field here. Drill-through works best when you want to show your reports in categories like, for example, first dashboard executive summary and then another for product detail and then one more for customer detail.
You need to drag your field here. I am dragging the product field here.
After dragging the field, you will get your products under that, and you will get an arrow to go to page one. From page one, if you click on any product from any visualization, it will take you to page two, and you will see the data according to that product, whatever you selected.
Hopefully, this has helped you in your PowerBI learning journey.