Forum Discussion
Sorting Data in Different Order Based on Slicer Selection in Power bi
Hi Everyone,
I want to sort the data in a specific order. When I select a value in the slicer.
Below is the raw data:
Table :
| Year | Month | Product | Amount |
| 2023 | 1 | AA | 100 |
| 2023 | 2 | BB | 200 |
| 2023 | 3 | CC | 100 |
| 2023 | 4 | DD | 100 |
| 2023 | 5 | EE | 300 |
| 2024 | 1 | AA | 300 |
| 2024 | 2 | CC | 500 |
| 2024 | 4 | DD | 600 |
| 2024 | 5 | ZZ | 300 |
| 2024 | 6 | BB | 300 |
slicer :
order Required
The order in which the products need to be sorted, when we select the year from slicer.
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
5 Replies
speedrampsSuper User
You description is a bit confussing.
Please provide example input data as table (not a screen print)
and the desire output
with a clear decsription
and omit any irreleant data and infomation.
you seem to be saying you want the data in C, E, B, D, A sequence instead of A, B, C, D, E
That is easy ...
just create a seperate table with 2 columns
Product Sortkey C 1 E 2 B 3 Then sort product by sortkey
learn how here
https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-sort-by-column
Please click [accept solution] and [thumbs up]
The issue is that, for example, Product "AA" is present in both 2023 and 2024. I want Product "AA" to be displayed in one order for 2023 and a different order for 2024.
I have attached an image showing the required sort order for 2023 and 2024. As you can see, the sort order for product 's in 2023 is different from 2024.
- speedramps
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
rohit1991Hi 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
-
