Matrix Column Sort Order

[jwjwjwjwjw]

My apologies if the solution for this is already out there, I don't think it is but I figured it wouldn't hurt to check.

I am using the matrix visual to compare year by year profitability of a range of products. Products go on the columns, with rows for various revenues and costs. The final row is profit, which I have conditionally formatted via an index column. The user could switch years with a a slicer. 

Currently the columns are sorted alphabetically by product name, which doesn't really do much for me. What I would like to do is order the product columns by profitability, with the most profitable on the left and least profitable on the right. With multiple years to slice through, I don't believe I can do a sort by column, due to products having a different profitability every year. 

At the moment I am using 3 different matrix visuals and grouping by importance/similar products for a more intuitive visualization of the product profitability. Of course it still doesn't order within the groups and multiple complex matricies ends up hogging resources in the final product. This seems like something that ought to be possible, but I can't figure it out. 

Does anybody have any ideas? Thanks in advance for any help you can provide

Thanks, 

jw

[Vmpbi]

Thanks for sharing… check out the links below. It provides a comprehensive guide on how to sort measures as columns 

in a matrix visual. 

https://insurancedatainsights.blog/2024/04/27/dynamic-data-display-matrix/ 

https://www.youtube.com/watch?v=k5FMfvPmkv4&t=190s 

[SrxRgm]

Here is an example of Price/lb on some Fruit and veggies in two diff Cities. 

In a Matrix, with PBI, how to you sort such a way that Denver prices display before Dallas prices, Column sort based on the value of the label (asc or desc)? 

I'd assume it's doable, just as yet not sure how.  

                                         Dallas     Denver

Apple	$                     2.00	$  2.15
Banana	$                     0.50	$  0.55
Carrot	$                     1.25	$  1.30
Celery	$                     1.75	$  1.80
Mango	$                     2.50	$  2.55
Spinach	$                     1.50	$  1.55

Anyone any direction? Thanks in advance. 

SR

[Anonymous]

Isn't it a shame and ridiculous that after... - what? 2 years? - we still can't sort by arbitrary columns in a matrix. Such a simple and basic thing. Microsoft - shame on you.

[MFelix]

Hi @Anonymous ,

This is related with the type of information you use on yhe columns or rows of the matrix. If they are individual columns on the model you can rearrange them has you need however if you are using a the data from a column to present your information on the matrix columns the used option is the one of the sorting of the data in that column.

I understand what people refer because in excel for example this is doable, but it's diferent softwares so some functionalities are different.

Check if there is an idea for this in the forum and vote for it.

[Todd_S]

This is still one of the top search results for this question, so I'm going to propose a solution that I came up with.  I used the Power Query editor.  It is NOT a dynamic sort for your matrix, but it can update as your data changes.

I want my Job Title columns to sort in descending order by number of FTE's (full-time equivalents). 

In Power Query editor (Transform data), Duplicate the query you want to add a custom sort to (in my case, Employee Roster).

Disable Load

Remove all columns but 2:  [Job Title], [FTE]

Group by Job Title, with a summation of FTE

Sort the [Sum FTE] column in descending order

Add an Index column (Optional:  renamed to [JT Sort Order])

Go back to the original Employee Roster query

Merge with your newly created query, Left Outer Join on [Job Title]

Expand the new column with the Table result, keeping only [JT Sort Order]

Close & Apply

In Table View, click on your Employee Roster [Job Title] column and set the sort by to [JT Sort Order]

-As mentioned above, the advantage of this is it will update each time your data changes; my HR department likes to create new titles.  However, it will NOT change dynamically on your Matrix.

-This is a small data table I am working with.  Not sure how well it will perform with, for example, a Sales table.  But the concept works.

[GDUb]

The article at https://community.powerbi.com/t5/Desktop/Matrix-Column-Head-Order/td-p/71572 talks about sorting by an index column. I was able to use that info to sort by a value column. It's not pretty, but it works. Here's the skinny:

1. In the Data view, in your measures folder, create a calculated measure for the value you want to sort by  [Sort Value] = SUM('MyTable'[Values]). 
2. Create a new calculated column [Sort Order]  in the table that is the source dimension for your matrix column category, and make [Sort Order] = [Sort Value]. 
3. On the "Modeling" tab of the Data view, click the "Sort by Column"  button and choose [Sort Order].
4. Go back to the Report view and add your matrix, adding row, column, and value fields. 
5. If the columns are sorted in the order you wanted, you win!
6. If they're sorted in the opposite order you wanted, go back to your sort order column in your column dimension table and change the formula to [Sort Order] = - [My Value].
7. Go back to the report tab, and the columns should now be sorted in the order you want. 

Note that this method sorts on the total value of the value column outside the context of the matrix, so it will not dynamically change based on values in the matrix, i.e. if product A has more sales than product B last year, but Product B is the winner this year, changing year filters won't cause the order of the columns to change, they still sort by total sales. I also don't know how it behaves with ties. I'm sure there's a way to write a context sensitive measure if you needed to, but this got me where I needed to be.

[Willgart]

you have to add the column field first and the measure after.

if you add the measure field before the column field, the column order will not be applied.

[v-frfei-msft]

Hi @jwjwjwjwjw,

Please check the case to find the ways to sort columns in matrix.

Regards,

Frank

[v-frfei-msft]

Hi @jwjwjwjwjw,

Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.

Regards,
Frank

[MFelix]

Hi @jwjwjwjwjw,

Matrix visuals can be sorted directly in the columns or using the 3 dots on the header, check if any of those possibilities work for you.

If they don't work can you share a small sample of the data and expected result, so i can check it.

Regards,

MFelix

[jwjwjwjwjw]

I want to sort matrix columns (the columns in the visual, not the columns in the data table) with a conditional value. You can do this with rows via a custom column (in the data table), but not columns as far as I can tell.

Item        Product3 Product 1 Product 2

Profit              7          5              2

It ought to be simple, but I don't think it can be done.