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!