How to Create Drill Down With Proper Sort Order In Power BI

About This Artical In Power Bi

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?

This article is the solution for this.

Let’s get started!

We have the following types of datasets. We have columns,

Year
Qty
Month
sales amount

Now, let us load the data in Power BI.

Step 1 :

Go to Home and click on “Transform Data”.

Step 2 :

Create a duplicate column of the Month and rename it as “Month-Sort”.

Click on “Close and Apply”.

Step 3:

Create a calculated column using the following formula.

SortNumber = SWITCH(Table1[Month-Sort],”JANUARY”,01,”FEBRUARY”,2,”MARCH”,3,”APRIL”,4,”MAY”,5,”JUNE”,6,”JULY”,7,”AUGUST”,8,”SEPTEMBER”,9,”OCTOBER”,10,”NOVEMBER”,11,”DECEMBER”,12)
Step 4:

Select the Month column.

From the ribbon, select Modelling and from the Sort by Column, select “SortNumber”.