07-24-2018 01:45 PM
Hello,I'm surprised I couldn't already find an answer to this question. I am working with this matrix table:
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!
07-25-2018 01:49 AM
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.
07-25-2018 01:18 PM
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!