How to switch columns order in a matrix table similar to pivot table fields

[SIMM]

Hello,

I have created a Matrix table with cagegories in the rows and years in columns with values of 2 different mertics (Sales Dollars & Sales Pieces).  In the columns it is currently displaying 2024 Pieces then Dollars followed by 2025 Pieces then dollars, what I am trying to do is switch the order to display 2024 pieces then 2025 pieces followed by 2024 dollars then 2025 dollars which is basic to do in Excel with moving about the pivot table fields values but can't seem to work out how to do in PBI and can't find info in Google search

This is how data is currently represented and you can visulised what I want Year Year pieces then Year Year Dollars

Thanks

[v-veshwara-msft]

Hi @SIMM ,
We wanted to kindly follow up regarding your query. If you need any further assistance, please reach out.
Thank you.

[v-veshwara-msft]

Hi @SIMM ,
Just wanted to check if the response provided was helpful. If further assistance is needed, please reach out.
Thank you.

[v-veshwara-msft]

Hi @SIMM ,
Thanks for reaching out to Microsoft Fabric Community.

To switch columns order in Matrix visual the methods shared by @MasonMA and @danextian are valid and effective.

Just checking in to see if you had a chance to further review the solutions shared and whether they were helpful.

If further assistance is needed, please reach out.

Thank you.

[SIMM]

Hello,

Applogies, i have been on holiday leave for a couple weeks.

I didn't have much luck with MasonMA as it was a bit convoluted considering have up to 6-7 years of data.  I will have a look at danextian one next week as i am in catch up mode now.

Many thanks

[danextian]

Hi @SIMM 

You can reorder a column using a custom sort, but values and measures in a Power BI matrix follow a strict hierarchy — with dimensions first, followed by the measures. The sequence of measures (e.g., Pieces or Dollars) depends on their order in the Values tile. Unlike in Excel PivotTables, Power BI does not allow moving values above other hierarchy levels - egs placing Pieces and Dollars before the year. The workaround is to use a disconnected table containing a column of measure names and reference that column to return the value of each measure - sort of materializing them in a column.

Revenue or Volume = 
SWITCH (
    SELECTEDVALUE ( RevVolume[RevVolume] ),
    "Revenue", [Total Revenue],
    "Volume", [Total Transactions]
)

Please refer to the attached file and the links below

Create dynamic format strings for measures 

Sort one column by another column in Power BI 

[SIMM]

hello, thanks for the note and excample, unfortunately it would not let me move the MetricYear to column

[MasonMA]

@SIMM 

Sorry, there was a bit of confusion last night.

In your scenario, you may need to first pivot out the 'Metric' column by creating one calculated column. I don't think it would have to be in a disconnected table unless you wanted to sort by 'Sales Piece' and 'Sales Dollar' and have a dollar sign on amount (as in my sample data)

In Matrix table place the 'Metric' above 'Year' Column to have 'Metric' shown on the first level of Column header, like below. 

Thanks

[MasonMA]

@SIMM 

Hi, true it's not as intuitive as Excel. below is one of some other ways you can try. (I used some sample data and you can access this file here)

1. Create one Calculated column in the table

MetricYear =
'Table'[Metric] & " - " & FORMAT('Table'[Year], "0000")

 2. Create another Calculated column for sorting.

MetricSort =
SWITCH (
    TRUE(),
    'Table'[Metric] = "Sales Pieces" && 'Table'[Year] = 2024, 1,
    'Table'[Metric] = "Sales Pieces" && 'Table'[Year] = 2025, 2,
    'Table'[Metric] = "Sales Dollars" && 'Table'[Year] = 2024, 3,
    'Table'[Metric] = "Sales Dollars" && 'Table'[Year] = 2025, 4,
    99
)

then in the Model view select this MetricYear Column and sort by MetricSort

From there you can build your Matrix Visual like below