Forum Discussion
Sort by Columns On Unpivoted dataset
Resident RockstarHi JonHacking ,
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 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
Hi v-piga-msft ,
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:
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.)
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.
e.g. From This:
Constant | Value A | Value B | Value C |
Con 1 | 3 | 0 | 0 |
Con 2 | 5 | 1 | 0 |
Con 3 | 4 | 2 | 1 |
To This:Constant | Value A ↓| Value B | Value C |
Con 2 | 5 | 1 | 0 |
Con 3 | 4 | 2 | 1 |
Con 1 | 3 | 0 | 0 |Hopefully this makes sense,
Jon- v-piga-msft
Hi JonHacking ,
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.
More details, please refer to my attachment.
Best Regards,
Cherry
Thank you for your response, In your example, how would you then sort by Value B whilst keeping Value A in the Visual?
Thnaks Again
Jon
