Forum Discussion
Sorting rows and columns in matrix visual
Hi,
hoping someone can help me. I have a matrix with columns and rows and calculated values.
My client wants to be able to sort the rows in a specific order and then the values in descending order so the column Starting a Grant would be at the far left. I've managed to sort the trading status in pie charts and bar charts with only 1 dimension by creating a measure for each trading status and then adding as values and re-ordering that way. I can't seem to find a weay of doing this for the matrix though.
The below is a chart I have used measures for each of the trading status
Any help would be much appreicated.
Debra
hi, Deb3107
Add a conditional calculate column by dax in Summary table
For example:
For sort=IF([Trading Status] ="Pre-Start",1
IF([Trading Status] ="Established 1-3 years",2
IF([Trading Status] ="Established 3-10 years",3
IF([Trading Status] ="Established >10 years",4))))
...
If there is other attribute, keep the same logic in the formula
Then use selected [Trading Status] filed in the right fields pane->Click Sort by Column->choose the new column.
https://radacad.com/sort-by-column-in-power-bi
Best Regards,
Lin
3 Replies
hi, Deb3107
You could get it by using the Sort by Column button in power bi desktop, for more detail see below:
https://radacad.com/sort-by-column-in-power-bi
Best Regards,
Lin
Hi v-lili6-msft
I don't appear to have the options when clicking on the 3 dots to revert back to default sort order so I get an error saying "this ccolumn can't be sorted by a column that is sorted,directly or indirectly, another column".
The table I'm trying to sort is a Summary table created by using the SUMMARIZE funtion so I can't use the Query editor either that is suggested here https://community.powerbi.com/t5/Desktop/Sort-by-another-column-error/td-p/61341
I'm at a bit of a loss of what to do now and reports need to go live this week.
hi, Deb3107
Add a conditional calculate column by dax in Summary table
For example:
For sort=IF([Trading Status] ="Pre-Start",1
IF([Trading Status] ="Established 1-3 years",2
IF([Trading Status] ="Established 3-10 years",3
IF([Trading Status] ="Established >10 years",4))))
...
If there is other attribute, keep the same logic in the formula
Then use selected [Trading Status] filed in the right fields pane->Click Sort by Column->choose the new column.
https://radacad.com/sort-by-column-in-power-bi
Best Regards,
Lin
