Forum Discussion
Matrix sort order based on second row
Hi,
I am trying to sort matrix second row but it's grouping first row and then sorting. Is there a way to override grouping and sort based on specific rows?
Data:
| Column Name | Period | Item | Value | Percentage |
| Col 1 | Jan'20 | Item 1 | 100 | 50% |
| Col 1 | Jan'20 | Item 2 | 200 | 50% |
| Col 1 | Jan'20 | Item 3 | 300 | 50% |
| Col 2 | Jan'20 | Item 1 | 50 | 80% |
| Col 2 | Jan'20 | Item 2 | 150 | 80% |
| Col 2 | Jan'20 | Item 3 | 250 | 80% |
| Col 3 | Jan'20 | Item 1 | 25 | 80% |
| Col 3 | Jan'20 | Item 2 | 125 | 80% |
| Col 3 | Jan'20 | Item 3 | 225 | 80% |
| Col 1 | Feb'20 | Item 1 | 101 | 51% |
| Col 1 | Feb'20 | Item 2 | 201 | 51% |
| Col 1 | Feb'20 | Item 3 | 301 | 51% |
| Col 2 | Feb'20 | Item 1 | 51 | 81% |
| Col 2 | Feb'20 | Item 2 | 151 | 81% |
| Col 2 | Feb'20 | Item 3 | 251 | 81% |
| Col 3 | Feb'20 | Item 1 | 26 | 81% |
| Col 3 | Feb'20 | Item 2 | 126 | 81% |
| Col 3 | Feb'20 | Item 3 | 226 | 81% |
Expected result: Col 1 should be last as per sort order but Matrix is grouping column name and then sorting.
Actual result:
Alternate approach: Sort order is working if I move Percentage as first and then Column Name. But interactions are not working because I have to pass only Column name to other visual. In this scenario percentage and column name are passed to other visual.
Expected Result: (Percentage Sort order is not working because of grouping column name)
Actual result: Percentage and column name are passed to top visual. But, I want to pass only Column name not percentage.
Hi ctspradeep ,
My workround is use a measure to "replace" the Column subtotals,
Please try this:
Measure = IF ( HASONEVALUE ( 'Table'[Item] ), SUM ( 'Table'[Value] ), MAX ( 'Table'[Percentage] ) )The final output is shown below:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi ctspradeep ,
My workround is use a measure to "replace" the Column subtotals,
Please try this:
Measure = IF ( HASONEVALUE ( 'Table'[Item] ), SUM ( 'Table'[Value] ), MAX ( 'Table'[Percentage] ) )The final output is shown below:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi ctspradeep ,
Actually, I'm not very clear about your issus, did you want to sort by Percentage column not by Name column in matrix ?
To my knowledge, sort works based on the ROWS field in matrix, and you could sort by different field via Drill-down like this:
Or refer to this thread:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi Anonymous ,
I would like to sort by Percentage column not by Name column in matrix and Percentage column should be in second position like below
