Sort by Columns On Unpivoted dataset

Question

I have a table as below:&nbsp;Consultant | Value A | Value B| Value C|....|Value Z|Con 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3|...|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5|I need a Table Visual Where you are able to add/remove Values Dynamically so have Unpivoted The 'Value' Columns To create the following dataset:Consultant| Value Type|Value|Con 1&nbsp; &nbsp; &nbsp; &nbsp; | Value A&nbsp; &nbsp; &nbsp;|&nbsp; &nbsp; &nbsp; &nbsp;0|Con 1&nbsp; &nbsp; &nbsp; &nbsp; | Value B&nbsp; &nbsp; &nbsp;|&nbsp; &nbsp; &nbsp; &nbsp;1|Con 1&nbsp; &nbsp; &nbsp; &nbsp; |Value Z&nbsp; &nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp;5|&nbsp;I've then built a matrix visual with Consultant as the row dimension, Value Type as the column dimension and Value as the Value dimension. I've then included Value Type as a Filter Visual so I can pick and Choose Columns.However, this removes the ability to sort based on an individual column.Is there any way to be able to choose columns dynamically whilst also being able to sort on individual columns?Any help would be appreciated,Thanks,Jon

v-piga-msft · Answer

Hi&nbsp;JonHacking&nbsp;,
I have a little confused about your requirement.
Is there any way to be able to choose columns dynamically whilst also being able to sort on individual columns?
What is the individual columns do your mean? Do you want to sort by column headers in matrix like below.

Actually, we cannot sort by column field in matrix directly in Power BI and the column is ordered by A-Z by default in matrix.
If you want to sort your columns in matrix, you could use Sort by Column&nbsp;under modeling tab in Power BI.
I made a sample which should be more obvious.
Assuming that you have the data like below and you want to create the matrix the unpivot data, then sort the column by month.

You need to add an index column after unpivot columns.
Then you need to sort month column by index column.

Here is the output.

Hope this can help you.
Best Regards,
Cherry
&nbsp;

jonhacking · Answer

Unfortunately no, it looks like it is not possible.

jonhacking · Answer

Hi&nbsp;v-piga-msft&nbsp;,&nbsp;Apologies, I realize what I said before was a bit vague and am unsure how to upload images without an image hosting service. So will try my best to clarify below:&nbsp;I have the report set up as per your first image, albeit with more than 1 constant (e.g. Con 1, Con 2, Con 3 etc.)&nbsp;What I want to be able to do is keep the Attribute filter, whilst being able to sort by the SUM of one of the matrix columns.&nbsp;e.g. From This:&nbsp;Constant&nbsp; | Value A&nbsp; &nbsp;|&nbsp; Value B&nbsp; |&nbsp; Value C&nbsp; |Con 1&nbsp; &nbsp; &nbsp; &nbsp;|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3&nbsp; &nbsp;|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 |Con 2&nbsp; &nbsp; &nbsp; &nbsp;|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5&nbsp; &nbsp;|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1 |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 |Con 3&nbsp; &nbsp; &nbsp; &nbsp;|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4&nbsp; &nbsp;|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2 |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1 |To This:&nbsp;Constant&nbsp; | Value A ↓|&nbsp; Value B&nbsp; |&nbsp; Value C&nbsp; |Con 2&nbsp; &nbsp; &nbsp; &nbsp;|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5&nbsp; &nbsp;|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1 |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 |Con 3&nbsp; &nbsp; &nbsp; &nbsp;|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4&nbsp; &nbsp;|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2 |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1 |Con 1&nbsp; &nbsp; &nbsp; &nbsp;|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3&nbsp; &nbsp;|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 |&nbsp;Hopefully this makes sense,Jon

v-piga-msft · Answer

Hi&nbsp;JonHacking&nbsp;,
By my tests, we cannot achieve your desired output directly in matrix, but we could create a measure to achieve that.
Measure = CALCULATE(MAX('Table2'[Value]))
Then you could drag the measure under Value Field to create the matrix like below and sort by measure.&nbsp;

More details, please refer to my attachment.
Best Regards,
Cherry

jonhacking · Answer

Thank you for your response, In your example, how would you then sort by Value B whilst keeping Value A in the Visual?&nbsp;Thnaks Again&nbsp;Jon

Forum Discussion

Sort by Columns On Unpivoted dataset

6 Replies

Related content

Unpivot Column

Unpivot columns

Pivot / Unpivot?

Unpivot Question

Unpivot