Forum Discussion
Sorting by another column - unique id - please help
[Reporting Category] should be sorted by [Reporting Category sort order]
[Reporting Subcategory] should be sorted by [Reporting Subgroup Sort Order]
Also, If your Reporting Subcategory column contains unique values (it looks like it does), then you can just use Reporting Subcategory column as the active relationship between this table and your fact table, instead of creating the Key column.
If you would like, you can send me a private message, or post here, a sample .pbix file with these two tables and as much data removed as you need to ensure your organizational security. I will solve the problem and return the .pbix file with the solution implemented.
I am trying to sort the Reporting Category in a custom order but the reporting category also has a subcategory (different column) that must be in a certain order as well. For example, gross profit contains both revenue and cost of revenue and revenue must come before cost of revenue, etc.
Create a new table with the category and subcategory, and add sort order columns. Follow along in this example and apply it to your situation.
For example, here are some various animals that fall into two categories (Mammal and Reptile), sorted with the Reptiles first then the mammals second, in the specific order that I want both the categories of Mammal and Reptile to be sorted, as well as the subcategories of Animal Name. In this table, I sorted "Family" by "Family Sort Order" and "Animal Name" by "Animal Sort Order"
This will act as a dimension table.
Here is a fact table containing the count of each animal.
Add a many-to-one connection on the smallest subcategory in your hierarchy. In this case, Animal Name.
Now, your visual, you can use the "Animal Name" column from the dimension table with "Count" from your fact table, then "sort by" Animal Name Ascending in the visual, and it will be in the correct order. Snake, Lizard, Cat, Zebra, Dog, Elephant. Note that this is not sorted alphabetically, not sorted by ascending Y axis value etc. It is sorting using the order I dictated in my dimension table.
Here is the data in a matrix, with the Category and Subcategory both sorted correctly.
See attached .pbix file.
EDIT:
If you have problems because your Subcategory values are not unique to each Category like they are in my animals example, follow these directions. For example if you have "Revenue" subcategory within both "Net Profit" category as well as "Profit" categories.
Add a "Key" column to both tables. (I did [Family] & "-" & [Animal Name]) to uniquely idenfify each combination of Family and Animal Name.
Add a Key sort order column with the order you want them sorted, taking into account both the categories and subcategories.
Then use that Key column to create the relationship.
etc etc...
///Mediocre Power BI Advice, But It's Free///
I have tried the stated above and it makes sense but when I tried it I continue to get the same error. See sorting table attached.
[Reporting Category] should be sorted by [Reporting Category sort order]
[Reporting Subcategory] should be sorted by [Reporting Subgroup Sort Order]
Also, If your Reporting Subcategory column contains unique values (it looks like it does), then you can just use Reporting Subcategory column as the active relationship between this table and your fact table, instead of creating the Key column.
If you would like, you can send me a private message, or post here, a sample .pbix file with these two tables and as much data removed as you need to ensure your organizational security. I will solve the problem and return the .pbix file with the solution implemented.
i didnt realize actually sorting the categories in power query would help. Thank you so much for your help!!