cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
mostvp123
Advocate V
Advocate V

How to sort values in a matrix by clicking on column headers

Hi,

I have a matrix in my report which has months on the columns (from my date table) and expense data on the rows by resource. It seems that the end user is able to sort the Total column by clicking on the column header, but not the individual month columns. Is this the way this is supposed to work? Is there any way to get the month columns to sort like the total column when clicking on the headers? Thanks for the help.Image1.JPG

9 REPLIES 9
Anonymous
Not applicable

If you want the even rows assorted in ascending order, and the odd rows assorted in descending order, then try the following

[m,n] = size(A);

for i=1:m
   if mod(i,2)~=0
       % row is odd
       A(i,:) = sort(A(i,:),'descend');
   else
       % row is even
       A(i,:) = sort(A(i,:),'ascend');
   end
end

The code iterates over each row and checks to see if that row is even or odd. If odd, then we grab all elements in that row (i for the row number, : for all columns) and sort it in descending order and place that sorted row back in the matrix. If even, then we do the same but sort in ascending order. Type help sort for details on this function.

Anonymous
Not applicable

Hi  @Anonymous ,

 

Thanks for replying.

I am not sure I understand how your solutioni would help. The problem is about the user not being able to click on the Column header and initiate a on-demand sort - quite similar to what you would find in a Table Visual.

 

sau001_0-1602365960027.png

 

 

Thanks,

Sau

 

ChandeepChhabra
Impactful Individual
Impactful Individual

@mostvp123 If you are expecting to (click on month) and sort the values in descending or ascending order then each month could have different sorting order. That is why Power BI doesn't allow that.

Alternatively you can sort by month if you drag the month in the row of the matrix visual

 

Hope this helps

 

Hi, 

Thanks for your response! Unfortunately I am a little confused. The months should not be in the rows as the business requirement is to have them in the columns. What do you mean by each month having a different sorting order? The values are expense data, and are numeric, so naturally they should sort either small to large (ascending) or large to small (descending) so each month would sort the same way, just like the Total column does. Thanks for the help!

@mostvp123 I understand what you are trying to say but I don't think this is possible. May be someone with a nifty trick can help

Can you share your Pbi file or a sample ?

No problem then I was just checking if I was doing something wrong, but yes my first thought is that the functionality is simply not available. Unfortunately I cant share the .pbix as it is full of sensitive data, but this happens in every matrix if a field is pulled into the columns, whether or not there is a total column. If the total column is included, one can sort this column but for some reason not the others. If the total column is removed, it is still impossible to sort the data. I think this functionality should be available - is there an idea for this posted?

hi, @mostvp123 

It is not available that sort values in a matrix by clicking on column headers for now.

here is Idea, you could vote and comment on it.

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/13808061-matrix-sort-columns-by-v...

 

Best Regards,

Lin

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

Hi @v-lili6-msft ,

I found your post useful. I am facing a similar problem.  Am I right to conclude that as of October 2020, Power BI Desktop does not yet support click and sort of Columns in a Matrix visual ?

 

Many thanks,

Sau

 

Anonymous
Not applicable

The ability to click on a column header and sort the results (as described by the author) is something I would definitely want.

I am sorry to see that Power BI has omitted such an useful feature.

 

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors