Power BI – Create Drill Up and Drill Down Reports

A Power BI report is a multi-perspective view of a dataset that includes visual representations of the data’s findings and insights. A report may contain just one image or numerous pages of images. You might be a person who designs reports or a business user who consumes reports, depending on your employment profile. Business users should read this article. Power BI service for business users Applies to. Reports can be created by Power BI service for designers & developers it requires a Pro or Premium license. Drill Up and Drill Down reports are used as visualizations. 

How to Create Drill-Up and Drill-Down Reports

The Microsoft Power BI service’s visual drill feature is demonstrated in this article. You can delve deeply into the specifics of your data by using drill down and drill up on your data points. Get the required Data Tables from here, viz. DimDate, which contains the date of the sale sorted out according to month, day, year, etc. And FactInternetSales, a dummy sales model through the internet and the related customer data.

Step 1: Select DimDate and FactInternetSales hierarchical data tables. And load the data model.

Load the data

Step 2: Drag the Fields to create visuals.

The data model is presented below:

Steps to Create Drill Up and Drill Down Reports

You can drill down into a visual’s hierarchy to uncover more information. It features a hierarchy, thus choosing one of the visual components (such as a bar, line, or bubble) would show a picture with progressively more details. For images with hierarchies, there are two ways to access the drill-down, drill-up, and expand functionalities. Utilize the one you prefer after trying both.

• To see and use the icons, first, hover your cursor over the image. Selecting the down arrow will enable the drill-down feature first. You can see that the drill-down option is enabled because of the grey background.
• The second method is to right-click an image to display and use the menu.

Step 1: Create the Sum of SalesOrderLineNumber hierarchical field visual column chart. As can be seen below drill-down option also gets enabled.

Step 2: Drill down or go to the next level in the hierarchy by clicking the downward arrow icon.

Step 3: We have drilled down up to the last level of the hierarchy, Sales according to Month wise sales. Let’s reach the root level or a prior level in the hierarchy by clicking the single arrow up icon for drilling up.

Step 4: Quarter-wise sales will be displayed. To get its previous level Another method is to right-click an image to display, use the menu and select Drill up.

Here we have arrived at the top-level hierarchy by drilling up. That is Yearly sales representation.

The matrix representation of the data is as follows,