pivotronics

If you have been working with matrix visual in Power BI, you might have seen that when you place a field in columns section, it’s sorted in ascending order by default.

Challenge: But if you need to sort it in descending order of the values in that field, there is no way that you can do it by way of configuring the visual from the UI.

Example: Here is a simple example of a matrix showing the month over month (in columns section) sales amount by each of the product categories (in rows section). Here, you see that the months in columns of the matrix are sorted in ascending order (i.e., from oldest to latest months). This is the default behavior of matrix visual in Power BI. But, what if you are to show these months in descending order (i.e., from latest to oldest months). Currently, there is no way of doing this by configuring the visual through UI (the way you could do it for sorting the values in rows section or the way you could do it in Excel Pivot).

Solution: Power BI has a feature of ability to sort the values of a field by the values in another field. Because the default behavior is values in columns section getting sorted in descending order, you can create a helper field for sorting the values in the sales month field by this helper field so that the sales months are sorted in descending order when placed in columns section of the matrix. But how do we calculate and populate values in this helper calculated column in a way that the for latest sales month it has the lowest value and for the next latest sales month it has next lower value and so on? To achieve this, the logic is to just take any large value that is at least larger than the largest value (latest date) in sales month field and then subtract the sales month from that value. i.e., in this example, since the latest sales month is 1-Jul-2021 you can take any date after 1-Jul-2021 as a constant and keep subtracting the values in sales month from this constant date. But since it is always possible to have the dates later than this 1-Jul-2021 very soon, you can take 31-Dec-9999 as a constant value instead of 1-Jul-2021.

Here is the Power Query M expression for the helper column:

= Table.AddColumn(RenamedColumns, "SalesMonthSorter", each #date(9999,12,31)-[Sale Month]),

In the power query step above, I added a new column to the table with the name “Sales Month Sorter”. #date is a power query intrinsic that takes, year, month and day enumerations as parameters and constructs the date constant.

Now use the Power BI’s ability to sort sales month column by this newly created helper calculated column (named as Sales Month Sorter in this example) as shown below from data view:

Now go to the visual view and see your matrix, sales months in columns section should have been sorted in descending order as needed (as shown below).

Caveat: If you are thinking of creating this calculated column in the model using DAX calculated column, note that when you try to sort “Sale Month” column with “Sales Month Sorter” calculated column that is created using DAX, it will result in circular dependency error. This happens when the column you want to sort is a candidate key of the table. so, it’s safe to use Power Query instead to create this sorting helper column.

Hope that’s a good short learning! Stay tuned for more!!