Sorting issue

[Jaspreet99]

Hi

I have a column with categories (for example: High, Medium, Low) but by default Power BI sorts them alphabetically.

I want them to appear in my visuals as High, Medium, Low (not alphabetical).

I tried using "Sort by Column" option, but it’s not working properly in my case.

[rohit1991 · Accepted solution]

Hi @Jaspreet99 

In Power BI, text fields sort alphabetically by default. To apply a custom sort order (High >> Medium >> Low):

1. Create a SortOrder column (numeric) like:

    High = 1

    Medium = 2

    Low = 3

2. Create column:

SortOrder =
SWITCH (
    TRUE(),
    Categories[Category] = "High", 1,
    Categories[Category] = "Medium", 2,
    Categories[Category] = "Low", 3,
     4
)

3. Select the Category column >> Column tools >> Sort by Column >> choose SortOrder.

Now visuals will show High >> Medium >> Low instead of alphabetical.

[MasonMA]

Or, in Power Query create a numeric column that represents the desired order

= Table.AddColumn(
PreviousStep,
"SortOrder",
each if [Category] = "High" then 1
else if [Category] = "Medium" then 2
else if [Category] = "Low" then 3
else 99
)

Then in Data view select the Category column, sort by Column 'SortOrder'.

[mizan2390]

You solve the problem in two ways. Either in Power Query or in Calculated Column. Better to do this in Power Query if your data model is large in size.
in Power Query,
Add Column >> Conditional Column >> Name the column like "Category Index" and set the value as required. 

or to create in Calculated Column

Table View >> Table Tools >> New Columns >>

Category Index=

SWITCH (

    TRUE(),

    Data[Category] = "High", 1,

    Data[Category] = "Medium", 2,

    3

)
now click on the category column, >>  go to sort by column >> selecet the Category Index , column to sort

If this solved your issue,  please Give a Kudo and Mark as Solution. Thank You