cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aasheesh1
Advocate II
Advocate II

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 13
Jellery87
New Member

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-s...
v-qiuyu-msft
Community Support
Community Support

Hi @aasheesh1,

 

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

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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 )
)
)

@aasheesh1

Could you post an excerpt of the relevant table?

Also, just to clarify, are you creating a measure or calculated column?


Owen Auger

Did I answer your question? Mark my post as a solution!

My Blog
Connect on Twitter
Connect on LinkedIn
Sean
Community Champion
Community Champion

@aasheesh1 

 

In response to an email I had received to take a survey on how to improve to Matrix/Table visualizations

One of the things I suggested to them in the survey was improved sorting options for the Matrix

I later posted that as a comment here... which you can see below

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/15844186-pivot-tables-and-breakdo...

Are you asking about something like this?

 

Please consider adding Sorting Options in the Matrix as those available for the Pivot Table which allows for sorting at EACH level.
For example in Excel if you have this Pivot Table setup:
Rows: Year, Type, Company and Location
Columns: Month
Values: Total Revenue (Measure)
In a Pivot Table you can sort at each level! (without disrupting the sorting at the other levels)
So you can sort your Year descending by Year, Type ascending by Type, Company also ascending by Company and lastly Location descending by Total Revenue. So in Excel you will always be looking at the current Year (on top), followed by Type and Company (A to Z) and only your Locations will be sorted based on Total Revenue best to worst. And by the way all previous years are collapsed only showing annual totals but you can always open a certain year and find what you are looking for. Now if you want to you can go and sort at the Company level but this time descending by Total Revenue (instead of by Company A to Z). Again Year and Type will not be disturbed you will get your current Year (on top) followed by Type (A to Z) and then the Companies will be sorted best to worst based on Total Revenue and the Locations for each Company will also be sorted best to worst based on Total Revenue. None of this is currently possible in the Matrix! Thanks!

 

EDIT: I didn't mention the Month Column but of course you can sort it also to show the Months either Jan to Dec or Dec to Jan!

For example in beginning of the Year you can sort Jan to Dec but closer to the end you can reverse the order Dec to Jan

so you see the most recent data first (meaning on the left side in the table).

BhaveshPatel
Community Champion
Community Champion

Tables only support single column sorting in  Power BI. For multi column sorting, use matrix visual.

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

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. 

Anonymous
Not applicable

Hi Bavesh,

 

how you do multiple sorting with matrix?

is multiple sorting in a matrix possible without using dax workaround?

To sort fields in the Matrix Visual: Fields Icon > Drag individual fields to Rows

Turn on Subtototals (optional): Format Roller > Row subtotals > Apply to labels (On) > Per Row Level (On) > Choose On for field(s) to be included in Subtotal

OGORs.gif

Did you ever get a solution to sorting by multiple columns in a matrix?

Thats unfortunate. Isn't there a work around using DAX and adding a third column to sort.

Hi @aasheesh1

 

Have a look at this post for a formula for a Rank based on two columns.

https://community.powerbi.com/t5/Desktop/How-to-Rank-a-list-based-on-2-values-double-rankX/m-p/44076...

 

You could use a measure like this for sorting possibly.

 

Owen

 


Owen Auger

Did I answer your question? Mark my post as a solution!

My Blog
Connect on Twitter
Connect on LinkedIn

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors