Forum Discussion
Sorting Data in Different Order Based on Slicer Selection in Power bi
Try this ....
Create a "driver table" ...
Click on Sortkey and unpivot other columns
Rename atttubute to Year
Rename Value to Product
Change the datya types
Add a M:M Prodcut relationship to your data
Create 2 measures
Sorted product = SELECTEDVALUE(driver[Product])Sales = CALCULATE( SUM(yourdata[Amount]), yourdata[Year] = SELECTEDVALUE(driver[Year]))Add Matrix visual
Change the row text colour to white
and change the Rows filedname to a single space
to hide it
user the Driver year and not yourdate year in the slicer ... otherwise the solutoion will not work
Please click [accept solution] and the thumbs up button. Thank you
Super UserHi Madhu7624 ,
The best way to handle sorting that changes by slicer selection (like by year) is to set up a custom mapping table that defines the sort order for each year and product combination.
Here’s how you can do it:
-
Build a Sort Mapping Table: Create a table with three columns: Year, Product, and SortOrder.
-
Create Relationships: Link both Year and Product from your main data table to Year and Product in your new mapping table.
-
Add a Year Slicer: Use the Year column as a slicer so users can pick the year they want to see.
-
Build Your Visual: Add Product (from your main table) and the SortOrder field (from your mapping table) to your matrix or table visual.
-
Sort By SortOrder Click on the column dropdown in your visual and choose “Sort by SortOrder.”
(You can hide the SortOrder column in the visual if you don’t want to show it.)
Now, when you change the year with the slicer, the products will sort in the custom order you defined for that year.
This method is clean, easy to maintain, and super flexible, just update your mapping table.
Power BI