Sorting Matrix rows - this should be easy

[davidlittle]

Hi, It must be me being daft but to me this should be easy... I have a pre-existing matrix in a report that someone else created and i am trying to change the sort order. The attached screenshot shows the three fields and their sources, i've also attached the relationships if its relevant.. But no matter what i change in the "Data" view "Sort By" column, it remains sorted by "channel" alphabetically .. I want it sorted by the year column numerically. I do have a date column in the same table that is not shown in the matrix and I have tried sorting by that but the matrix doesnt change at all no matter what I do -

What am I doing wrong ?

Where do i go to see what it is currently sorting by and how do I change it ?

sorry if this is a simple quesiton

 

[hnguy71]

Hi @davidlittle ,

Select the matrix visual, click the three-dots at the top right, and click Sort By and click your Year column:

If that doesn't work, try changing your year from a text to a number.

[davidlittle]

@hnguy71 Thankyou for quick reply! this allows me to sort it numerically by year which works and is brilliant thankyou but I overlooked this doesnt sort  by the other columns ? By which I mean the data is grouped by year and month, so ideally i want it sorted by year, then month - can I add "tierd" sorting ?

[hnguy71 · Accepted solution]

@davidlittle ,

Yes if it's from the same dimension (table). If you want it by Year and Month then you should replace those two columns with a Month-Year column and have that column sorted by your Date field. If you don't already have a column for that, it could easily be created by adding a new calculated column:

Mnth-Year = FORMAT([Date], "MMM YYYY")

[davidlittle]

Aha,  I added the column you mention but couldnt set "sort by" date on it as the date column had multiple values per month year grouping (as it would do) BUT building in your solution i added a calculated sort coulmn and then sorted the visual by that ! so thank you very much for your solution as it allowed me to fix it it!

Mnth-Year-sort = VALUE(Dates[Calendar Year] & "00") + Dates[Calendar Month]