Reply
Regular Visitor
Posts: 16
Registered: ‎12-18-2017

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!

Community Support Team
Posts: 2,535
Registered: ‎02-06-2018

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

Highlighted
Regular Visitor
Posts: 16
Registered: ‎12-18-2017

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!