Others

How to Custom Sort by Brand and Subtype in Power BI

Khadija Khan Oct 15, 2026 869 views

Question details

The user wants to sort subtypes in a specific custom order for each brand in a Power BI slicer or visual, particularly when the same subtype belongs to multiple brands.

Product: Power BI
Device & OS: not provided
Scenario: Organizing hierarchical data in a visual or slicer where standard alphabetical sorting does not meet business reporting requirements.
Observed behavior: Attempting to sort by a column from a different table often results in a sorting error if the Brand and Subtype combinations do not have a uniquely defined sort-order value.

Before you start

Ensure you have access to edit the data model in Power BI Desktop and that your dataset contains clearly defined columns for both Brand and Subtype.

Solution 1Recommended

Create and Apply a Custom Sort-Order Table

Use this method to establish a dedicated dimension table that defines an exact numerical sorting sequence for every Brand and Subtype combination in your model.

Power BI requires a one-to-one relationship between the category being sorted and the numerical value dictating its sort order. When dealing with overlapping subtypes across multiple brands, creating a dedicated reference table ensures the engine can uniquely identify the hierarchy.

Create the sort-order table

Generate a new table in your dataset containing three specific columns: Brand, Subtype, and Sort Order. Assign a unique numeric value in the Sort Order column for each distinct Brand and Subtype pairing.

Import into the data model

Load this new reference table into your Power BI data model and ensure appropriate relationships are established with your primary fact tables.

Configure the visual

In your Power BI canvas, insert a slicer or visual and populate it using the Subtype column exclusively from your newly created sort-order table.

Apply the sort by column feature

Select the Subtype column in the Data pane, navigate to 'Column tools' on the top ribbon, click 'Sort by column', and choose your 'Sort Order' column.

Resolve uniqueness errors

If Power BI throws a multiple-value sorting error, verify that the sorting column resides in the exact same table as the column being sorted. If necessary, use Power Query or DAX to concatenate the Brand and Subtype columns into a combined key to guarantee unique values.

Create and Apply a Custom Sort-Order Table

Troubleshooting Sort Errors: A common mistake is attempting to sort a column in one table using a sort-order column located in a different table. Always keep the text column and its corresponding numeric sort column within the same table.

Free Microsoft Office alternative

Simplify Your Data Analysis with WPS Office

While Power BI is a robust tool for enterprise business intelligence, WPS Spreadsheet provides a lightweight, completely free alternative for everyday data sorting, pivoting, and visualization. Easily organize your datasets without encountering complex data modeling errors.

1. Download and Install: Visit the official WPS website to download and install WPS Office Free on your computer or mobile device.
2. Import Your Data: Open WPS Spreadsheet and load your existing Excel or CSV data files seamlessly.
3. Sort and Visualize: Navigate to the Data tab to apply custom sorting hierarchies, create PivotTables, and generate professional charts in seconds.

100% compatible with Microsoft Excel (.xlsx, .xls, .csv) file formats.Built-in advanced sorting, filtering, and PivotTable features for rapid data analysis.Lightweight application that runs smoothly on standard PCs, Macs, and mobile devices.Familiar, intuitive user interface that requires zero learning curve for Office users.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Power BI display an error when I try to sort by another column?

This error occurs when the column you want to sort has multiple corresponding values in the target sort column. Power BI requires a strict one-to-one relationship between the text value being displayed and the number defining its sort order.

Can I perform a custom sort without creating a completely new table?

Yes, if your existing main table already has a dedicated column that provides a unique numerical value for every category combination, you can use that directly. However, for complex overlapping hierarchies like Brand and Subtype, a dedicated dimension table is highly recommended.

How do I create a combined key in Power BI for sorting?

You can create a new Custom Column in Power Query by concatenating text, or use a DAX calculated column formula such as `CombinedKey = [Brand] & "-" & [Subtype]`. This generates a unique identifier that prevents sorting conflicts.

Office Quick Solutions