Matrix sort order based on second row

[ctspradeep]

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.

[Anonymous · Accepted solution]

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.

[ctspradeep]

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

[Anonymous]

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:

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.