Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jwjwjwjwjw
Advocate II
Advocate II

Matrix Column Sort Order

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

10 REPLIES 10
SrxRgm
Frequent Visitor

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
Not applicable

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.

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.


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Todd_S
Advocate I
Advocate I

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
Advocate I
Advocate I

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
Helper II
Helper II

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
Community Support
Community Support

Hi @jwjwjwjwjw,

 

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

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
MFelix
Super User
Super User

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

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.