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.
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.
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
- Now put the “MatrixSortDescYear” in columns section in Matrix and remove previous “Year” column
Voila! Easy, simple and neat!
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.
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
Fabulous! Thank you for this tip. Very helpful
Thank you for article. It’s very useful.
Hi Poonam,
Welcome to BIInsight.com.
Happy that you found this post helpful.
Cheers.
Simple, yet very effective!! Thank you for the post.
i did this, but I get circular dependency error. Anyone faced similar error? it says circular dependency created between the year column and the new column
Hi Siva,
Thanks for sharing your issue.
I think you are using two calculated columns that have interdependencies.
If that’s the case then your issue is not related to the technique explained in this post.
At the end of the day, you have to resolve the dependencies regardless.
Cheers.
I got that too – at first. To be clearer: you need to create _two_ new columns, not one. The first is a _copy_ of the original, the second is its negative. The circularity comes if you create only the -ve new column.
Grazie mille
La mia casistica era esattamente questa
You’re welcome Luca.
I’m glad you found this post helpful.
Cheers
Thank you. This is really good article. I didnt see this technique of date sort anywhere
This post is brilliant and it’s exactly what aI was looking for! Only thing my “Year” contains financial years instead of calendar years therefore I am dealing with a text field having data such as “2022/23” instead of numbers such as 2022, that preventing me to set second column as a negative of first one.
Do you have any further suggestions for this kind of exception? Am I forced to transcode my Years’ data or is there any other workaround allowing me to leave Year field as it is?
Many thanks in advance for your contribution and best regards!
Andreas
Hi Andreas,
Welcome to biinsight.com.
Your Date table must have at least a column with Date or DateTime datatype, which shows the calendar date.
You can then add as many descriptive columns as you require, such as Year (integer), Month name (text), Month order (integer), Financial year (text), etc…
Without a column containing calendar date your Date table is not complete, so I suggest you add the date column (if you don’t already have it), then add a new column containing Year (integer) values.
Hopefully that helps.
Cheers