Sort Column Order

[enswitzer]

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:

[v-rzhou-msft]

Hi @enswitzer 

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

[v-rzhou-msft · Accepted solution]

Hi @enswitzer 

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. 

[amitchandak]

@enswitzer , 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

[dedelman_clng]

Hi @enswitzer  - 

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