If you are looking for a Microsoft Power BI Corporate Training in Gurgaon , please feel free to reach out at info@instrovate.com or whatsapp at +91-9953805788
Today we will explore drill in and drill out hierarchy. we can tell Power BI that we want to drill the data, and can control up to which level we want to drill in. We can do it by using Drill menu under Visual tools and we will see different aspect of record accordingly.
We have following options for drilling in/out:
1.Drill down: When we will do Drill down for any particular bar it will drill down to next level of the selected bar and we can further drill down the records so on till the atomic level of transaction. For example, like if we have a bar chart and it displays different bars for years 2014, 2015, and 2016, now select drill down sign and click on bar for year 2014, it will further drill down records and display results in quarter’s and now if I select Qtr1, it will further drill down to the month level of that quarter, on selecting any month from this list leads us to the days level of that specific month. So now we will be seeing results by year, Quarter, month and day level only for year 2014.
2.Drill up: when we reached (e.g. at days level in above example) the last level of Drill down we can go back and drill up from drill up sign. Using Drill up sign we can go up to the last level data aggregation. While doing drill up we do not need to select any record on the chart, we need to click on drill up sign only.
3.Show next level / Go to the next level in the hierarchy: Using this sign we can see a collectively report for all data. For example, if we have data for years 2014,2015 and 2016. Now if we select this sign (next to the drill up sign) it will take us a collectively result as quarters for all years. In quarter1 we will have results for all year ‘s (2014, 2015 and 2016 year’s) quarter 1 and so on. Now if we further click on hierarch it will display result by months and then by days. So here we will have results for days from all years e.g. if we select 1 day it will be displaying results for all months from all years for date as 1.
4.Expand next level / Expand all down one level in the hierarchy: on clicking on this sign we can see all data at the same time at atomic level for all years. Like if we have a bar chart having results for year 2014, 2015,2016. On clicking this sign it will leads us to the results on quarter level for all years separately e.g. 2014 Qtr1, 2014 Qtr2, 2015 Qtr1, 2015 Qtr2 so on and on further clicking on same sign it will display result for years with month level and on further drilling leads us to the last transaction date with month, quarter and year. It is called continuous data results.
Once we are done with results we can select Drill up sign to go back to the main level. i.e all year’s level from where we have started.
Now let we see all these results by practically for Power BI. Load a sample file in Power BI:
Select Quantity and the Order date from the data set to create bar chart.
Drill down:
Select Drill down from the menu or from the down arrow on the chart from left corner and click on any bar line from the bar chart, in below screen we have selected 2014 year record:
Click on 2014 bar line and it will further drill into the data from 2014 year. In below screen we can see records from quarter of year 2014
Now we can see detail about respective bar’s by moving cursor. Click on the Qtr 1 record and it will display detail about quarter 1 of year 2014:
Click further on January record and it will display record from January 2014 :
For row ID 7981, we have same detail in report for this day.
Drill up:
Now click on the Drill up arrow until we reach the last step for drill up.
Show next level / Go to the next level in the hierarchy:
We have bar chart with Order date and Quantity fields:
Now I can see ‘Go to the next level in the hierarchy’ sign from report:
Click on Data/Drill:
Now click on Show next level or on ‘Go to the next level in the hierarchy’ sign from report ‘clicking this it will show results for all year in Qtr form:
Again, click on this sign, now it will display results by months level for all Qtr:
Click on same menu again:
Here we can see results by date ( for all year’s for all months) .
It is the last level for hierarchy.
We can see data for this report, by clicking on see records
Now we can select Drill up to go back to the initial level.
