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 that 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 sources and Power BI files 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 “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 a Year-QTY-Month drill-down in Power BI.

Stay connected with me for amazing articles.