Overview
In Power BI, we can achieve drill-down functionality. Power BI facilitates Year-QTY-Month drill down automatically for the columns which have the Date data type.
Now, there are some situations in which we do not have a Date Column available in our dataset. At that time, what to do to achieve the drill down?
I have added some sample data source and Power BI file on GitHub. You can download it using the following link.
https://github.com/Dhruvinshah16/Power-BI/tree/master/PBI-Year-QTY-MonthDrillDownSample
This article is the solution for this.
Let’s get started!
We have the following types of datasets. We have columns,
Year
Qty
Month
Now, let us load the data in Power BI.
Step 1
Go to Home and click on "Edit Queries".
Step 2
Create a duplicate column of the Month and rename it as “Month-Sort”.
Please refer to the following screenshot.
Click on "Close and Apply".
Step 3
Create a calculated column using the following formula.
SortNumber = SWITCH(Table1[Month-Sort],"Jan",01,"Feb",2,"Mar",3,"Apr",4,"May",5,"Jun",6,"Jul",7,"Aug",8,"Sep",9,"Oct",10,"Nov",11,"Dec",12)
Step 4
Select the Month column.
From the ribbon, select Modelling and from the Sort by Column, select “SortNumber”.
Step 5
Now, let’s test the result!
Top Level - (Year)
First Level - (Qty)
Second Level - (Month)
Conclusion
This is how we can create Year-QTY-Month drill-down in Power BI.
Stay connected with me for amazing articles!