cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ctspradeep
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 NamePeriodItemValuePercentage
Col 1Jan'20Item 110050%
Col 1Jan'20Item 220050%
Col 1Jan'20Item 330050%
Col 2Jan'20Item 15080%
Col 2Jan'20Item 215080%
Col 2Jan'20Item 325080%
Col 3Jan'20Item 12580%
Col 3Jan'20Item 212580%
Col 3Jan'20Item 322580%
Col 1Feb'20Item 110151%
Col 1Feb'20Item 220151%
Col 1Feb'20Item 330151%
Col 2Feb'20Item 15181%
Col 2Feb'20Item 215181%
Col 2Feb'20Item 325181%
Col 3Feb'20Item 12681%
Col 3Feb'20Item 212681%
Col 3Feb'20Item 322681%

 

Expected result: Col 1 should be last as per sort order but Matrix is grouping column name and then sorting.

Actual result:

ctspradeep_0-1618009671995.png

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)

ctspradeep_3-1618010093667.png

Actual result: Percentage and column name are passed to top visual. But, I want to pass only Column name not percentage.

ctspradeep_1-1618009989874.png

 

1 ACCEPTED SOLUTION
Eyelyn9
Community Support
Community Support

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:

marix sort.PNG

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.

View solution in original post

3 REPLIES 3
Eyelyn9
Community Support
Community Support

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:

marix sort.PNG

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.

View solution in original post

ctspradeep
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

ctspradeep_0-1618240964094.png

 

 

Eyelyn9
Community Support
Community Support

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:

sort by in matrix.PNG

 

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.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors