Forum Discussion

CJ-Flag's avatar
CJ-Flag
Frequent Visitor
2 years ago
Solved

How to Sort Columns in a Matrix Visual

Hello,

 

I currently have - hopefully - a small challenge. My matrix visual is defined to be sorted by rows. Now I have a field that names the columns from another table where the values are summed and counted. Now, I also want to dynamically sort the columns by the highest values of the summation.

 

 

how cani solve the use case?

 

Thanks a lot for upcoming Support!

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi CJ-Flag ,

     

    If you are trying to sort the columns based on the following total value.

     


    1. first you can create a calculated column to get the total value.

    Column =
CALCULATE (
SUM ( financials[ Sales] ),
FILTER (
ALL ( financials ),
'financials'[Month Name] = EARLIER ( 'financials'[Month Name] )
)
)

     

    2. Then create a calculation table and create a one-to-many relationship with the original table.

    Table =
SUMMARIZE('financials','financials'[Month Name],'financials'[Column])

     

    3. Make the month name column in the calculation table sorted according to the column column.

     

    4. Place the corresponding fields in the matrix.

     

    If your Current Period does not refer to this, please clarify in a follow-up reply.

     

    Best Regards,

    Clara Gong

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

4 Replies

  • Shravan133's avatar
    Shravan133
    Super User
    2 years ago

    To dynamically sort columns in a Power BI matrix visual by the highest values of a summation, you can follow these steps:

    1. Create a Measure for Summation:
      • First, ensure you have a measure that calculates the summation of the values you want to sort by. Let's call this measure TotalValue.

    TotalValue = SUM('YourTable'[YourValueColumn])

    1. Create a Rank Measure:
      • Create a measure to rank the columns based on the summation values.

    ColumnRank =

    RANKX(

        ALLSELECTED('YourColumnTable'[YourColumn]),

        [TotalValue],

        ,

        DESC,

        DENSE

    )

    1. Add the Rank Measure to the Matrix Visual:
      • Drag your columns field to the Columns area of the matrix visual.
      • Add the ColumnRank measure to the matrix visual as a value.
    2. Sort the Columns by the Rank Measure:
      • Click on the column header in the matrix visual to sort by ColumnRank.
      • This should dynamically sort the columns by the highest values of the summation.

     

  • danextian's avatar
    danextian
    Super User
    2 years ago

    Hi CJ-Flag,

    Matrix rows can be sorted by a value field as below:

    Please note that  sorting will be applied to the totat and there's no option to sort it by the individual column field value (jul, aug, sep, etc).

    • CJ-Flag's avatar
      CJ-Flag
      Frequent Visitor
      2 years ago

      Hello,

      thank you very much for your reponse.

      You are right but u are changing the sorting of the rows.

      the rows have to fix, so a,b,c or 1,2,3

      and depending on that the columns have to been sorted depending on the sum of the values.

      u have a hint how to solve this?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi CJ-Flag ,

     

    If you are trying to sort the columns based on the following total value.

     


    1. first you can create a calculated column to get the total value.

    Column =
CALCULATE (
SUM ( financials[ Sales] ),
FILTER (
ALL ( financials ),
'financials'[Month Name] = EARLIER ( 'financials'[Month Name] )
)
)

     

    2. Then create a calculation table and create a one-to-many relationship with the original table.

    Table =
SUMMARIZE('financials','financials'[Month Name],'financials'[Column])

     

    3. Make the month name column in the calculation table sorted according to the column column.

     

    4. Place the corresponding fields in the matrix.

     

    If your Current Period does not refer to this, please clarify in a follow-up reply.

     

    Best Regards,

    Clara Gong

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.