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
pmdci
Advocate V
Advocate V

matrix table: sort columns in descending order

Hi,

 

I have created a matrix table to show the sales figures for my sales reps (rows), by week number (columns).

 

Problem is that the week numbers are being displayed in ascending order (1 to 52). Is there a way to show the week order in descending order instead?

 

13 REPLIES 13
ipsingh79
Frequent Visitor

Hi,

You cannot achieve  this reverse sorting directly... here is a workaround:

1. Go to the Edit Query option of the Week dim table.

2. Apply Sort desc on the unique week code column (there must be a unique identifier for each week i.e. in my case I have WeekCode column as 20170101, 20170102, 20170103, 20170104, 20170205, 20170206.... YYYYMMWW where WW is the incremental week number of the year from 1 to 52.

3. Select 'Add Index column'  from Add Column menu and provide Starting Index=1 and Increment=1 in the dialog box. 

4. Rename column as appropriate.

4. Save Apply and Close the Edit Query

5. Use this Index as 'Sort By Column' wherever required.

Anonymous
Not applicable

This wont work in Direct Query mode. Is there another option

Hi ipsingh79,

 

I tried your suggestion but get an message saying

 

Sort by another column

We can't sort the 'Date' column by 'Index'. There can't be more than one value in 'Index' for the same value in 'Date'. Please choose a difference column for sorting or update the data in 'Index'.

 

Based on this message, I duplicated the 'Date' column, set Data Type: Whole Number, and sort 'Date' by this new field, but the Matrix still displays the oldest Date on the Left.  

Hi Ink20a2,

 

My bad I forgot to update the post. 

So here is the working solution:

1. Make sure your table has some column as FinancialWeekCode i.e. YYYYMMWW where WW is the WeekNo e.g. ...,20170103, 20170104, 20170205,... 

2. Add custom column as : SorttWeekCode = 99991253 - [FinancialWeekCode]

3. Apply and Close the query editor.

4. Sort the Week Name column with SortWeekCode custom column.

 

Let me know if you still get  any issue.

 

Added Custom column

Thanks ipsingh79 that worked.  I had been missing the step 2. as adding index hadn't worked, but the subtraction in that step gave the higher number to sort on.  Brilliant!

I tried this but received the message

 

Sort by another column

 

We can't sort the 'Date' column by 'Index'. There can't be more than one value in 'Index' for the same value in 'Date'. Please choose a different column for sorting or update the data in 'Index'.

 

 

Based on this error message, I duplicated the 'Date' column and set Data Type: Whole Number.  Sorted the 'Date' by this field, with no change.  Matrix still displays the oldest week on the left.  Any other suggestions?

 

rseiwert
New Member

I too have this issue. It is common that when using Dates as Columns to have the most current on the left and decend to the right.

vasim07
Helper I
Helper I

Here, maybe you can find a solution.

Actually I was looking for a way to sort the column from largest to smallest by Quarter in the matrix. Mean that user can click on the particiular quarter column, the data will short accorrdinly to Asc or desc order in the matrix.

 

Quarter2015Q32015Q4
CountryCostCost
A113311041
B22462221
C20712051
D4934120
kcantor
Community Champion
Community Champion

In your pbix file or source file, make sure that the weeknumbers are listed as data type date or number in order for the sort to work. If it is text, it may struggle.

NOTE: i just re-read this and see where I made an incorrect assumption.  Please disregard.





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

Proud to be a Super User!




Anonymous
Not applicable

Hello @pmdci

 

If your matrix looks like this one (week instead of month in my matrix), you just have to click to the week header and the sort should be inverted.

 

Sort1.png

Sort 2.png

Sébastien

Hi,

 

Actually no... The rows are the salesperson and columns are the week numbers.

I'm also looking for this solution. Anyone know how to workaround this. This can be done in Excel but not in Power BI Desktop

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.