Forum Discussion
multi column sort
How can we sort a table using two or more columns?
I want to sort by fiscal year order first and then by size of deal next (large to small).
13 Replies
Hi Anonymous,
In your scenario, you can try the DAX suggested by OwenAuger. Also you can add a third column like this, and sort table based on this column. See: Sort a table by the results of two columns .
Column 3 = ([Column 1] * 100) + (1/[Column 2])
Besides, you can also vote on this idea item: Add a multiple columns sort featue.
Best Regards,
Qiuyun Yu
Hi OwenAuger, v-qiuyu-msft,
Thanks for your replies. If im not wrong, these work if both the columns have values.
In my case, I have one column wit Text (Fiscal Period) and then values. I tried to use below DAX, but got the following error -
"A single value for column 'Sort By Fiscal Year' in table 'SFDC' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."
Any idea how to correct this?
Final Rank = RANKX (
ALL (SFDC),
RANKX ( ALL (SFDC), SFDC[Sort By Fiscal Year],, ASC )
+ DIVIDE (
RANKX ( ALL (SFDC), SFDC[Value USD],, DESC ),
( COUNTROWS ( ALL (SFDC)) + 1 )
)
)Anonymous
Could you post an excerpt of the relevant table?
Also, just to clarify, are you creating a measure or calculated column?
- We need an update here!
Seems crazy that this is a standard feature in excel, but can’t be handled in power BI!
Vote the idea up and let’s get progress
https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/11185431-add-a-multiple-columns-sort-featue Tables only support single column sorting in Power BI. For multi column sorting, use matrix visual.
Did you ever get a solution to sorting by multiple columns in a matrix?
Hi Anonymous
Have a look at this post for a formula for a Rank based on two columns.
You could use a measure like this for sorting possibly.
Owen
What would you recommend in the cases where a matrix won't work? In other words, when I have data that I do not wish to summarize in any way, a matrix is impossible to use.
