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

[JasperN]

Hi everyone,

I've been searching for a solution online without success, and I'm starting to think there might not be one.

However, before giving up, I wanted to check if someone here can prove me wrong.

Scenario

I have a matrix visual in a Power BI report:

• Rows: "ArticleName" column from the dimension table D_Article.
  (In reality, it contains multiple levels, but I've simplified it for this example.)
• Values: A measure that sums values from the Revenue column in the fact table F_Sales.
• Columns: A field from a table that contains all weeks where a specific selection of articles was available for customers to purchase. This is derived from a referenced dimension table D_Catalogue, where only distinct available weeks are retained (formatted as "DHyyyyww", e.g., DH202502, DH202503 for weeks 02 and 03 of 2025).

What Works Well

• The headers are automatically added when new weeks become available.
• The article list is dynamically filtered to show only the articles available for the selected weeks.

The Issue

I need to sort the matrix ascending or descending based on the values of the measure in a specific week column (so the "DHyyyyww" columns, see screenshot below).

However, Power BI does not allow sorting on measure values in columns directly (by clicking on the headers).

Since clicking on the headers to sort isn't possible, I'm open to alternative solutions, such as allowing users to select the sort column from a dropdown or any other workaround.
But as for now, I'm unable to implement any workaround on my own.

Measure Used in Values

Salesvalue Catalogue =
VAR SelectedCatalogue = SELECTEDVALUE('t_CatalogueSelection'[Catalogue])
RETURN
IF(
NOT(ISBLANK(SelectedCatalogue)),
CALCULATE(
SUM('DWH F_Sales_Catalogue'[TotalRevenueEUR]),
'DWH D_Catalogue'[Catalogue] = SelectedCatalogue,
'DWH F_Sales_Catalogue'[FlagActive] = TRUE()
),
BLANK()
)

 

Screenshot of matrix (redacted)

 

Question

Does anyone have an idea how to achieve this? Any workaround that allows sorting dynamically would be greatly appreciated!

Thanks in advance!

[JasperN · Accepted solution]

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.

[v-venuppu]

Hi @JasperN ,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please accept it as a solution and give it a 'Kudos' so other community members with similar problems can find a solution faster.

Thank you.

[v-venuppu]

Hi @JasperN ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

Regards,

Rama U.

[v-venuppu]

Hi @JasperN ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

Regards,

Rama U.

[amitchandak]

@JasperN , In Matrix visual you can sort on column totals. You can not sort on individual column values of the field used in the column's fields