How to Sort Columns in a Matrix Visual

[CJ-Flag]

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!

[Anonymous · Accepted solution]

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.

[danextian]

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]

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?

[Shravan133]

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.