cancel
Showing results for
Did you mean:
Regular Visitor

## 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.

1 ACCEPTED SOLUTION
Community Support

My workround is use a measure to "replace" the Column subtotals,

``````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 3
Community Support

My workround is use a measure to "replace" the Column subtotals,

``````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.

Regular Visitor

Hi @Eyelyn9 ,

I would like to sort by Percentage column not by Name column in matrix and Percentage column should be in second position like below

Community Support

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:

https://community.powerbi.com/t5/Desktop/Sorting-single-value-column-descending-by-latest-week-endin...

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.

Announcements