Forum Discussion
How to sort matrix columns descending?
I have a matrix with a text field as the Row field, and an integer as the Column field. The columns display in ascending order. I want the columns to show in descending order. There does not seem to be an option to sort the matric by the column field.
How do I achieve this?
You'll have to make your sort column in the query editor to avoid the circular dependency error. [Age]* -1 like you did before but in query.
Regards,
Pat
7 Replies
- mahoneypat
Microsoft Employee
You'll need to make a Sort By Column for the column used in the Matrix columns. Please see this article.
Quick Tips: How to Sort Matrix by Column in Descending Order (biinsight.com)
Regards,
Pat
mahoneypat In the Data tab, I have done this:
created a new calculated column called NegAge as = -[Age]
tried to set the Sort By Columns on the Age Column to be NegAge. But this results in an error "A circular dependency was detected"
Instead, I tried setting the [Age] Column sort option to "Sort descending". But none of that changes the [Age] columns in the matrix from displaying in ascending order.
- MattAllington
Community Champion
I assume you mean the columns of data. This is not possible because it is a table of data, not a set of 18 independent columns. Each has their own sort order
MattAllington, SSRS does this just fine. In the Column Group settings you can select which data field should be used to sort the Column Headers in the matric visual.
I want to know how to achieve the same in Power BI.
- mahoneypat
You'll have to make your sort column in the query editor to avoid the circular dependency error. [Age]* -1 like you did before but in query.
Regards,
Pat
- v-easonf-msft
Community Support
Hi， Anonymous
Try to create a Rank column as below:
Rank = RANKX('Table','Table'[Age],,DESC,Dense)To avoid "A circular dependency......",please create another column 'Age2'
Age 2 = 'Table'[Age]Then sort column ‘Age2’ by column "Rank":
The result will show as below:
Please check attached pbix file for more details.
Best Regards,
Community Support Team _ Eason
