Overview:-

Power BI is a dashboard reporting tool, also an user friendly tool which can get inputs from various data sources (SQL Server, excel, csv, analysis services and text files) and generate a graphical and meaningful dashboards. In general

Report – is a single visual representation

Dashboard – is a two or more reports/visuals together in same page

Power BI Desktop has 3 tabs mainly Reports, Data and Model (as shown below)

i) Model – is a data source view diagram to view all imported tables objects from any one of the input data sources

ii) Data – place where you can preview the sample data of the imported table objects

iii) Reports – for creating dashboards/reports from the model table objects

Following are the other tabs which are used to support the Model, Data and Reports tab

1. Data Sources – used to get the Input data for Dashboards/Reports
2. Visualization – used to show the data in more readable and understandable formats
3. Fields – contains the table objects along with Measures (names) and Values (sales, score)
4. Filters – used to filter data on page level or visual level

Below is the sample SQL data consists of 3 tables which has employee’s Mathsmark, Interest and Sales data. Execute this script in your SQL Server Management Studio before start creating Power BI dashboard, it acts as a Data input for Power BI Dashboard.

Create Database Power_BI
GO

---Employee's Mathsmark---
drop table if exists Mathsmark
create table Mathsmark(
	EmpName Nvarchar(1000),
	Score Int
)
Insert into Mathsmark (EmpName,Score) 
values ('John',50),('Mac',35),('Lion',70),('Tiger',62),('Pigeon',23),('Alex',92),('Jack',84)

---Employee's Interest---
drop table if exists Interest
create table Interest(
	EmpName Nvarchar(1000),
	Games Nvarchar(1000)
)
Insert into Interest (EmpName,Games) 
values ('John','Cricket'),('Mac','Football'),('Lion','Hockey'),('Tiger','Football'),('Pigeon','Cricket'),('Alex','Chess'),('Jack','Football')

---Employee's Sales---
drop table if exists Sales
create table Sales(
	EmpName Nvarchar(1000),
	SalesNo Int
)
Insert into Sales (EmpName, SalesNo)
values ('John',550),('Mac',305),('Lion',730),('Tig
er',610),('Pigeon',260),('Alex',950),('Jack',870)

In this blog, we will see how to create Power BI dashboard with Line Chart, Pie Chart, Stacked Column Chart, Matrix, Table and Donut Chart, Let’s see step by step procedure for creating the Dashboard.

Step 1:- Importing of SQL Data to Power BI Desktop, select “SQL Server” from “Data” tab and give your Servername and Database as “Power_BI” and click “OK”

Step 2:- Check all 3 table check boxes and click “Load”, you can also view the preview data by selecting the table object

After clicking the “Load” button, you can view the data model of “Power_BI” database in the “Model” tab, objects relationship are created automatically by the Power BI tool, all the table objects fields in displayed in right side “Fields” tab

You can also view table’s preview data and objects names, fields in “Data” tab

Step 3:- Select “Line Chart” in Visualizations tab and check the fields in “Mathsmark” table and also sort the line chart by “EmpName” field

Step 4:- Select “Pie Chart” in Visualizations tab and check the “Games” fields in “Interest” table and drag “EmpName” field to values tab, it will display the no. of employees interested in which games

Step 5:- select “Stacked Column Chart” in Visualizations tab and check “Sales” table fields, as mentioned in above here I’ve sorted the data by “EmpName” Field

Step 6:- select “Matrix” in Visualizations tab and check the fields (EmpName, Score and SalesNo)

Step 7:- select “Table” in Visualizations tab and check the fields (EmpName and Games) from “Interest” table

Step 8:- select “Donut Chart” in Visualizations tab and check the “EmpName” field from “Interest” table and “SalesNo” field from “Sales” table

Now we will see how to filter data in dashboards/reports, in Power BI we have 3 level of filtering (Report/Visual, Page and All Page), please note that the report/visual level filter will appear only after selecting page level filter.

here we have used the page level filter in which (EmpName =”Jack”) is excluded, now the dashboard is showing all Employee’s data expect “Jack”

Happy Learning !! Please share it with your friends If you like this post.