Quick Tips: How to Sort Matrix by Column in Descending Order

How to Sort Matrix by Column in Descending Order

Today Microsoft released Power BI Desktop March 2020 which I was hoping that it includes a simple feature on Matrix visual to be able to sort the Martix by column in descending order, but, it doesn’t. So, in this post I quickly show you how to sort Matrix by column in descending order.

Here is the scenario. One of my customers is building a report in Power BI showing sales by Year, Month and Day of Week in a Matrix as below.

Sorting Matrix Visual in Power BI
Sorting Matrix Visual in Power BI

Everything looks fine! But looking at the Matrix sorting quickly reveals that such feature is NOT available (YET). But the customer would like to see the Matrix sorted by Year in descending order, something like this.

Sorting Matrix by Column Headers in Descending Order
Sorting Matrix by Column Headers in Descending Order

Here is the solution which is super simple.

  • Add a new “Year” column, either in Power Query or using DAX. I use DAX just to keep it as simple as possible
MatrixSortDescYear = 'Date'[Year]
  • Add another column which keeps the order of the new “MatrixSortDescYear” in descending order like below:
MatrixSortDescYearOrder = -( 'Date'[Year] )
  • Sort the first column by the second one
Power BI Desktop Sort Column by another Column
Power BI Desktop Sort Column by another Column
  • Now put the “MatrixSortDescYear” in columns section in Matrix and remove previous “Year” column
Sort Matrix by Column in Descending Order

Voila! Easy, simple and neat!

4 thoughts on “Quick Tips: How to Sort Matrix by Column in Descending Order

    1. Hi Jorge,

      Thanks for your feedback.
      That’s absolutely right. Well, filtering is one the most important foundations of DAX, so we have to take extra cautious when we deal with a column sorted by another column.

      Cheers.

      1. Hi Soheil

        Thanks for the article. I am having a similar requirement.
        I am displaying ticket count (like sales count in your case) with rows being ticket type and column being days(Moday to Friday) in matrix.

        (1) My rows are fixed -> ticket type is arrived based on ascending order of tickets grouped for that given week.
        (2) within these ticket types pre-arrived, I want the columns to be sorted in descending order based on the value of ticket count. For example if Thursday has the maximum count it will come at the leftmost corner and other days follow it in descending order.

        I tried using a new column ” ticketcount = count(sheet1[ticketnumber])
        and sorted it using “ticketorder = – count(sheet1[ticketnumber]) but the required result is not coming. Is there any way to get this done

        Regards
        Krithika

Any thoughts? Share it with us here:

This site uses Akismet to reduce spam. Learn how your comment data is processed.