Sort Matrix Columns by Values

[DatamirHub]

I have this matrix view

I want my users to have the ability to sort by any column they want (e.g. rev of 2024 or 2023, or cap of 2024, ... etc)

I cant click on the columns to sort
when i go to the 3 dots on top right and sort by that i have only the ability to sort by the metrics which always sort by the last year values not a specific year value

is there a workaround to this I know some articles mentioned that not possible in Power BI

[MFelix · Accepted solution]

Hi @DatamirHub ,

I have create a work around based on some slicers and a calculation group but this can depend on the way you have your semantic model.

In my case I have the following table:

Add the following measures:

Cap Value = SUM('Data Values'[cap])

Rev Value = SUM('Data Values'[rev])

I have also created the following two disconnected tables:

Then I created a calculation group with the following code:

Calculation item =
VAR _measure =
    SELECTEDVALUE ( 'Measures filter'[Measure] )
VAR _year =
    SELECTEDVALUE ( 'Year filter'[year] )
VAR _temptable =
    CALCULATETABLE (
        ADDCOLUMNS (
            'Data Values',
            "valuetotal", SWITCH ( _measure, "cap", [Cap Value], "rev", [Rev Value] )
        ),
        'Data Values'[year] = _year
    )
RETURN
    IF (
        NOT ( ISINSCOPE ( 'Data Values'[year] ) ),
        SUMX ( _temptable, [valuetotal] ),
        SELECTEDMEASURE ()
    )

Now if you add the measures to your values and apply the calculation group you get the following result:

Has you can see the totals have in this case the values for the rev value 2023, if I changed it to 2024 it gets the inverse order because I'm doing the order by total:

It does not matter wich of the column of the total you select because they will be equal.

Then you can hide the totals column and it continues to sort in the same way:

Only question that I did not address here was the highest to lowest or lowest to highest not sure if you want it.

See pbix file attach.

[DatamirHub]

Thanks, @MFelix this is interesting I will try it out if it works as expected, I will reach out to get an idea of how to do the highest to lowest or lowest to highest

[MFelix]

Hi @DatamirHub ,

For the highest to lowest add the following table:

Now updated the calculation group to:

Calculation item = 
VAR _measure =
    SELECTEDVALUE ( 'Measures filter'[Measure] )
VAR _year =
    SELECTEDVALUE ( 'Year filter'[year] )
VAR _sorting =
    SELECTEDVALUE('Sorting Order'[Sort])

VAR _temptable =
    CALCULATETABLE (
        ADDCOLUMNS (
            'Data Values',
            "valuetotal", SWITCH ( _measure, "cap", [Cap Value], "rev", [Rev Value] )
        ),
        'Data Values'[year] = _year
    )
RETURN
    IF (
        NOT ( ISINSCOPE ( 'Data Values'[year] ) ),
        SUMX ( _temptable, [valuetotal] )  * _sorting,
        SELECTEDMEASURE ()
    ) 

Now the total column will be properly sorted:

See PBIX file attach.

[DatamirHub]

Hey @Shahid12523 and @rohit1991 Thank you both for the workarounds

Unfortunatelly I have to stick with a matrix and i cant add a year filter because I already have two field parameters table one for the year and one for the measures
and the year filter can have multiple years but we want only to sort by specific year 

Thanks !

[Shahid12523]

Power BI Matrix visuals don’t support sorting by individual columns like “Rev 2024” directly. But here’s a quick workaround:
Use a slicer to let users choose which year/category to sort by.
Create a dynamic measure that changes based on slicer selection.
Sort your rows using that measure (via RANKX or hidden column).
Direct column-click sorting isn’t possible—only Table visuals support that.

[rohit1991]

Hi @DatamirHub 

Could you please try below steps:

• Unpivot your data in Power Query so that “Year” and “Measure (Rev/CAP)” become rows instead of columns. Then you can build a table visual and users will be able to click and sort by any Year/Measure combination.
• Or, create a separate table visual for each Year if column-level sorting is critical.
• If the requirement is interactive sorting, another option is to build a field parameter or a slicer that lets the user choose which Year/Metric they want to sort by, and then apply that dynamically to the Matrix/Table.