Forum Discussion
Sort Column Order
I have created a matrix visual that shows Target and Actual Dates. However, when I display these, it auto sorts them in alphabetical order such that the Actual date is on the right of the Target date. How can I show these in the reverse order (all the tutorials I have found have been for numbers rather than text)
Below are visualization set up and example of how my data is stored:
Hi Anonymous
amitchandak's idea is right, but build a calculated column and sort Target & Actual column by new Calculated Column will show error as below.
I build a Table like yours to have a test.
If sort Target & Actual column by calculated column like amitchandak's.
You can add a conditional column in Power Query Editor.
Or M Query
= Table.AddColumn(#"Changed Type", "Sort", each if [#"Target & Actual"] = "Target" then 1 else 2)Then Sort the Target & Actual column by this sort column.
Result is as below.
You can download the pbix file from this link: Sort Column Order
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Anonymous , in your table, create a new column
Target sort = if([Target & Actual] = "Target", 1, 2)
and mark this a sort column
https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column
Hi Anonymous -
This is by no means an "elegant" solution, but it works.
Create a new "dimension" table that has two columns: "Target & Actual" and "SortOrder". Something like
Tar-Act = SUMMARIZE(Table, Table[Target & Actual], "SortOrder", IF([Target & Actual] = "Target", 1, 2) )Create a relationship between this new table and your fact table (1 to many), based on Target & Actual.
Finally, highlight "Target & Actual" in the new table, go to Column Tools, select "Sort By Column" and choose "SortOrder". Put "Target & Actual" from the dimension table into the matrix, and it should sort the way you'd like.
Hope this helps
David
Hi Anonymous
amitchandak's idea is right, but build a calculated column and sort Target & Actual column by new Calculated Column will show error as below.
I build a Table like yours to have a test.
If sort Target & Actual column by calculated column like amitchandak's.
You can add a conditional column in Power Query Editor.
Or M Query
= Table.AddColumn(#"Changed Type", "Sort", each if [#"Target & Actual"] = "Target" then 1 else 2)Then Sort the Target & Actual column by this sort column.
Result is as below.
You can download the pbix file from this link: Sort Column Order
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous
Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.
Best Regards,
Rico Zhou
