Matrix sorting

[Anonymous]

Hi, 

I am trying to create a report page which should be dynamic and could be exported as you want.

For that I am using Fields parameter in matrix visual. Client wants to select Product Manager or other and then Date which is basicly YYYY-MM and sort it by Sales as show in picture. The problem is that he would like to sort first row level (Product Manager) and still have the second level (Date 
) in order month by month.

Is there a solution for that? 

Thank you!

[Anonymous]

Hi @Anonymous 

Is the following output you want?

You can create a measure 

e.g 

Measure 2 = IF(ISINSCOPE('Table'[Column1]),RANKX(FILTER(ALLSELECTED('Table'),[Column3] in VALUES('Table'[Column3])),CALCULATE(SUM([Column1])),,ASC),IF(ISINSCOPE('Table'[Column3]),RANKX(ALLSELECTED('Table'),CALCULATE(MAX('Table'[Column3])),,ASC,Dense)))

Then put it to the matrix and select sort by measure2 and ascending

and if you don't want to show the measure, you can close the text wrap of the column header, then hide the measure.

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Looks good and almost what I wanted. 

The only thing is, that I would like to have sorting like in your last picture, when on the top there is the most Sales made Manager (b in your picture) but Dates still goes from lowest (for example: in order 2023-01, 2023-08, 2023-09 for b).

What should I change in Measure 2?

Thank you!

[Anonymous · Accepted solution]

Hi @Anonymous 

You can try the following measure

Measure 2 = IF(ISINSCOPE('Table'[Column1]),RANKX(FILTER(ALLSELECTED('Table'),[Column3] in VALUES('Table'[Column3])),CALCULATE(SUM([Column1])),,ASC),IF(ISINSCOPE('Table'[Column3]),RANKX(ALLSELECTED('Table'),CALCULATE(MAX('Table'[Column3])),,DESC,Dense)))

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.