Forum Discussion

Jaspreet99's avatar
Jaspreet99
Advocate I
11 months ago
Solved

Sorting issue

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's avatar
    rohit1991
    11 months ago

    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.

3 Replies

  • rohit1991's avatar
    rohit1991
    Super User
    11 months ago

    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's avatar
    MasonMA
    Super User
    11 months ago

    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's avatar
    mizan2390
    Super User
    11 months ago

    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