Drill down report in Power BI

In previous post “Rise of the Power BI“, we have discussed about the Power BI and it’s components. In this post “Drill down report in Power BI”, we are going to learn how we can create a drill down report in Power BI.

To demonstrate the Power BI drill down functionality, we are loading the sample data using a SQL query from “AdventureWorks2014” database. Power BI desktop provides a direct connectivity to SQL Server which we are going to use in this tutorial.

Lets, load the data into power BI to create a drill down report:

Loading data into Power BI from SQL Server

To create a SQL Server connection to pull data from AdventureWorks2014 Database, follow below steps:

1. Open Power BI Desktop application.
2. A dialog box will appear at startup screen, click on “Get Data” button appearing at left of the dialog box. Optionally, we can also click on “Get Data” button in Power BI toolbox.
3. Select “SQL Server Database” from the list of the data sources and click on “Connect” button.
4. Another dialog box will appear. Put the server and database names in the respective text boxes (database name and SQL Statement values are optional here). In this example, I am using “localhost” in Server name and “AdventureWorks2014” in database name. A SQL query is used to fetch the data from Product, product sub category and product category tables.
5. On next screen, we will be asked to provide credential to connect the database. We can choose windows or SQL Server authentication as per our available credentials. Provide the credentials and click on “Connect” button. You may get an “Encryption Support” message, click on “OK” button to proceed.
6. You will be asked to provide the connection settings at this step; Import or Direct Query. Import option will import the data into Power BI and store it internally whereas Direct query option will fetch the data at run time. Choose it accordingly.
7. As per the information provided in server name, database name and SQL Statement, list of databases, tables or the output of the query will appear on the next screen. Click on Load button.
8. Now we have the required data / meta data inside the Power BI, which can be used to create reports.

Creating drill down report in Power BI

To create a drill down report in Power BI, we have used the below query to generate our data:

SELECT 
P.Name AS ProductName, PSC.Name AS ProductSubCategaroy, PC.Name AS ProductCategory, CAST(SUM(SOH.SubTotal) AS NUMERIC(18, 2)) AS TotalAmount
FROM Production.Product P
INNER JOIN Production.ProductSubcategory PSC ON P.ProductSubcategoryID = PSC.ProductSubcategoryID
INNER JOIN Production.ProductCategory PC ON PSC.ProductCategoryID = PC.ProductCategoryID
INNER JOIN Sales.SalesOrderDetail SOD ON SOD.ProductID = P.ProductID
INNER JOIN Sales.SalesOrderHeader SOH ON SOH.SalesOrderID = SOD.SalesOrderID
GROUP BY P.Name, PSC.Name, PC.Name

We have a data set in Power BI generated from the above query. When we load the data using a query, be default Power BI renames it as “Query1” (Query followed by the total number of queries), Rename it with some useful name to identify the underlying data. Below is the image:

To create a drill down report, follow the below steps:

• Click on “stacked column chart” visual in “Visualizations” section.
• Click on the “ProductCategory” field and “TotalAmount” field to add “TotalAmount” (as it is a numerical field) in value section of the visual and “ProductCategory” in axis section. Alternatively you can drag and drop these fields in value and axis section respectively. Power BI automatically recognizes the numerical fields and considers them as measures.
• To create a drill down report, we need to drop “ProductSubCategory” field and “ProductName” field just below the “ProductCategory” field in axis section. We should have the values and axis settings for the column chart like below:

• Click on the drill down button in column chart located at the top right corner to enable the drilling functionality of the column chart visual. Now, click on any category vertical bar to drill down at sub category level, to drill down at product level, click on any sub category vertical bar.
• To drill up, click on the drill up button located at the top left corner of the column chart visual.
• Click on drill all to next level button to drill all vertical bars (categories) to next level. We can see the drill down, drill up and drill all to next level in below image:

When we enable drill down for a visual, interactive sorting will not be working for the time. That means when we click on a vertical bar, other visuals on the same report will not be filtered and we will be drilled down to the next level of details.

To download and start creating your stunning reports and dashboards for free, visit Power BI website and download Power BI desktop application.

Thank you for the reading and please do share and rate this post. Input your comments and suggestions in comment section of this post.