cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
efliberty Regular Visitor
Regular Visitor

Sort Values in a Matrix

Hello,I'm surprised I couldn't already find an answer to this question. I am working with this matrix table: 

 

Capture.PNG

The values shown are % of the column total for each category. I want to sort these values by the most recent month. As you can see from the image I'm able to sort the TOTAL column, but how can you sort by one of the other columns? 

 

As a side note, it's not important to me to keep the row/column totals. 

 

Thanks for any help!

2 REPLIES 2
Community Support Team
Community Support Team

Re: Sort Values in a Matrix

Hi efliberty,

 

In your senario, I would recommend you to use a table chart and create a calculate column to achieve the total values every category using DAX like this pattern:

 

Total =
CALCULATE ( SUM ( Table[Values] ), ALLEXCEPT ( Table, Table[Category] ) )

Then you can click date column to sort other columns.

 

Regards,

Jimmy Tao

efliberty Regular Visitor
Regular Visitor

Re: Sort Values in a Matrix

Hi Jimmy, 

 

Thank you for your reply! I'm not sure I understand the logic behind that formula (I'm still learning DAX). In this example I'm looking at survey data, collected monthly. Respondents are asked to describe a product and I want to know what % of responses pertain to each product, each month. So the rows are the products and the value of the table is the count of responses, show as a % of column total. Can you explain why you would use SUM and ALLEXCEPT for this? 

 

As a side note, the picture I provided is a summarized version of the table with the data grouped into a handful of products. The actual table has about 300 products. Would I have to make a custom column for each? Or am I making a custom column for each month? 

 

Thank you for your help!

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 204 members 2,236 guests
Please welcome our newest community members: