Sorting a matrix visual based on measure values and dynamic header values

Sorry it took so long to respond, v-venuppu and amitchandak.

Basically, after much trail and error, I have 2 imperfect solutions for this problem!

Option 1 - Quite cumbersome

1. First I create a disconnected table "t_CatalogueSelectionSort" which is just a copy of the "DWH D_Catalogue" table mentioned in my starting post where I keep all the possible Week values (format "DHyyyyww") I would like to be able to display. (So just the 1 column in 1 disconnected table.)
2. I add a separate slicer for this new column (field), which is able to select 1 value of the column of the table I just created.
3. I create this additional measure which calculates the sales for only the selected value in the new slicer:

   • SelectedCatalogueSales =
     
     VAR SelectedCatalogue = SELECTEDVALUE('t_CatalogueSelectionSort'[Catalogue])
     RETURN
     
     CALCULATE(
         SUM('DWH F_Sales_Catalogue'[TotalLineAmount]),
         'DWH F_Sales_Catalogue'[D_Catalogue_ID] IN
             CALCULATETABLE(
                 VALUES('DWH D_Catalogue'[D_Catalogue_ID]),
                 'DWH D_Catalogue'[Catalogue] = SelectedCatalogue,
                 'DWH D_Catalogue'[FlagActive] = TRUE()
             ),
         'DWH F_Sales_Catalogue'[FlagActive] = TRUE()
     )

4. I add the new measure to the matrix as a value, in addition to the measure I already had to display the sales for each week that the user selected in another slicer (see starting post).
5. I sort the matrix on the newly added measure and do my best to hide the additional column (resizing to the smallest possible width, change the font color to match the background, etc.)
6. Now my users can select the specific weeks they want to see in the matrix, as well as sort on a specific one by selecting everything in 2 slicers.
7. Bonus for ease of use: add this measure to the new disconnected table "t_CatalogueSelectionSort" and add it to the slicer to filter on only showing the values where this measure equals 1. This will cause the slicer used for defining the sort column to only show the selected weeks in the first slicer.
   
   

   • ShowCatalogueSortSlicer =
     
     VAR CurrentCat = SELECTEDVALUE('t_CatalogueSelectionSort'[Catalogue])
     RETURN
     
     IF (
         CurrentCat IN VALUES('t_CatalogueSelection'[Catalogue]),
         1,
         0
     )

Downsides of option 1

1. You add an additional column which will always be slightly visible and which your users are able to manipulate (e.g. increase the width). This can be confusing.
2. It's quite a convoluted approach where you need 2 slicers to get the matrix you want.
3. When new weekvalues (format "DHyyyymm") are added, the second sort column is not automatically resized like I did with the existing ones. So there is a small effort required on a weekly basis (or whenever a new week value is added).

Option 2 - Hardcode everything

1. Install Tabular Editor in order to be able to add 150 new measures in one go.
2. Create the following measure for each possible value of "DHyyyymm" for 2023, 2024 and 2025.
   Example for DH202301:
   
   

   Revenue - DH202301 =
       CALCULATE(
           SUM('DWH F_Sales_Catalogue'[TotalLineAmount]),
           'DWH D_Catalogue'[Catalogue] = "DH202301",
           'DWH F_Sales_Catalogue'[FlagActive] = TRUE()
       )

3. Create a parameter which contains all of these measures
4. Add this parameter to the Values box of the matrix, while leaving the Columns box blank.
5. Also add this parameter to a slicer, so the user can decide which weeks they want to see.
6. This results in the cleanest approach where the user can just click a header and sort on whatever they want.
   I even added an AverageSales measure and SalesTrend measure which can also be sorted on!

Downsides of option 2

1. It's all hardcoded, so you can probably imagine the struggle.
2. I have groups of sales which are not week-based and are added each week. So I need to update the report every week to reflect the new values by adding new measures.
3. Customer wants to automatically show everything for the current year, but I haven't found a way to reliably do this. Especially since he wants the last week with sales on the most left position in the matrix which is countary to where Power BI adds new columns when selected...
   So it's all manually updated week by week.

Conclusion

I eventually went for option 2 as this was the cleanest approach for the end-user.

However, it does come with a routine weekly update & a big one for each new year...

Microsoft should really add this basic functionality to the matrix visual.