Sorting Data in Different Order Based on Slicer Selection in Power bi

[Madhu7624]

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.

[rohit1991]

To sort data based on slicer selection in Excel:

1. Create Sorting Table:  Map products to the required order for each year.

2. Add Helper Column: Example formula: =IF(A2=2023, VLOOKUP(B2, SortingTable!$A$1:$C$6, 2, FALSE),
   VLOOKUP(B2, SortingTable!$A$1:$C$6, 3, FALSE))
   
   

   Use VLOOKUP or INDEX-MATCH to fetch the sort order dynamically based on the slicer-selected year.

3. Link Slicer: Connect the slicer to filter data by year.

4. Sort Data: Sort using the helper column.

[Madhu7624]

Power BI

[speedramps]

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]

[Madhu7624]

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 · Accepted solution]

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