Forum Discussion

DatamirHub's avatar
DatamirHub
Advocate I
1 year ago
Solved

Sort Matrix Columns by Values

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

Need Help
  • MFelix's avatar
    MFelix
    1 year ago

    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.

6 Replies

  • rohit1991's avatar
    rohit1991
    Super User
    1 year ago

    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.
  • Shahid12523's avatar
    Shahid12523
    Community Champion
    1 year ago

    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.

  • MFelix's avatar
    MFelix
    Super User
    1 year ago

    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's avatar
      DatamirHub
      Advocate I
      1 year ago

      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's avatar
        MFelix
        Super User
        1 year ago

        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's avatar
    DatamirHub
    Advocate I
    1 year ago

    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 !