Search

Create Year Quarter Month Drill Down With Proper Sort Order In Power BI

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!