Forum Discussion
Sort Column in based on the Row total values in Matrix visual
Hi Team,
Please help me with the below usecase.
In the attached Power BI Matrix image, the column headers are not fixed categories there can be more new ones in future ( so I want the column headers to be sort by desc based on the Total row value ).
As the date is filtered on the page the position of the columns will change as per the Total % (heighest to the left & lowest to the right).
Measure used :
CTR= Divide(Clicks, Impressions,0)
Output: CB | TQ | QW | AS | ER | AS | YW
Thanks in advance,
Skn
You can sort Matrix columns dynamically based on Row Total using a Rank measure + Sort by Column.
Since your column headers are dynamic, we need to rank them by the Total CTR value.
**SOLUTION:**
**Step 1: Create this Measure**
This calculates total CTR for each column
Total CTR =
VAR _CurrentCol = SELECTEDVALUE('Your table name')
RETURN
CALCULATE([CTR], ALL('YourTable'))[YourColumnHeader][YourRowFie
ld]
**Step 2: Create Rank Measure for Sorting**
Column Sort Rank =
RANKX(
ALLSELECTED('YourTable'),
[Total CTR],
,
DESC,
DENSE
)[YourColumnHeader]**Step 3: Apply the Sort**
1. Click on your `YourColumnHeader` field in the Fields pane
2. Go to `Column tools` tab > `Sort by column` > Select `Column Sort Rank`
**Result:**
Now columns will automatically sort from Highest Total to Lowest Total: CB | TQ | QW | AS | ER | YW
When new columns are added in future, they will also sort automatically based on Total row value.
**Important Notes:**
1. Replace `YourTable` with your table name
2. Replace `YourColumnHeader` with OD, YW, CB etc field
3. Replace `YourRowField` with LHR-PAR, PAR-LHR field
4. Turn OFF "Show items with no data" for Column field
This works because `RANKX` recalculates on every filter change, so when date filter changes, column order will also change.
Let me know if you need help with the exact field names. Happy to help!
7 Replies
This scenario it is important to start with a key point: in Power BI, sorting column headers dynamically in a Matrix based on a measure (such as the Total of CTR) is not a native feature. The Matrix visual allows dynamic sorting for rows, but not for columns based on measure values.
One possible workaround is to use an auxiliary table where the sort order is predefined by the user, and then apply this order using the “Sort by column” feature.Dim_Channel_Sort =DATATABLE("Channel", STRING,"SortOrder", INTEGER,{{"CB", 1},{"TQ", 2},{"QW", 3},{"AS", 4},{"ER", 5},{"YW", 6}})
Steps:- Create a relationship between the auxiliary table and your fact table using the column used in the Matrix columns.
- Select the Channel column in the auxiliary table.
- Apply Sort by column → SortOrder.
- Use this Channel field in the Matrix Columns section.
However, this approach has an important limitation: the sorting remains static. If filters such as date change the values of CTR, the column order will not update automatically. Therefore, this method only works when a fixed business order is acceptable.My suggestion is to consider using a chart (for example, a bar or column chart) instead of a Matrix for this requirement. Charts allow sorting by measure values dynamically, which means the order will adjust correctly when filters are applied or when new categories appear.Summary:- Matrix does not support dynamic column sorting by measures.
- Auxiliary table with “Sort by column” is possible but static.
- A chart visual is the most practical solution when dynamic sorting is required.
DISCLAIMER: While I wrote a draft of this answer, I used Copilot to create a longer, more detailed step-by-step description to make it easier to apply.Since you want the sorting to be dynamic based on a measure, keep in mind that the measure value can change depending on the slicers and filters applied to the report. Because of that, I don't believe there's a way to dynamically sort columns based on a measure.
If the measure isn't affected by slicers or filters, then you could achieve the desired sorting by creating a dynamic table instead.
Hi,
As mentioned earlier, the Category (dimension) will keep on adding in feature, its not static.
I doubt there might be some workaround to handle this.
Please do share if any idea pop up in mind.
Thanks in advance!
SknSUMESHKUMAR22 just a suggestion , please tag the person to which your reply refers to especially when there are more than one reply.
I agree predefine sort will not work and there is no solution toachieve your result. My last reply is the one which is only possibility but that locks on that you cannot make it dynamic.
You can sort Matrix columns dynamically based on Row Total using a Rank measure + Sort by Column.
Since your column headers are dynamic, we need to rank them by the Total CTR value.
**SOLUTION:**
**Step 1: Create this Measure**
This calculates total CTR for each column
Total CTR =
VAR _CurrentCol = SELECTEDVALUE('Your table name')
RETURN
CALCULATE([CTR], ALL('YourTable'))[YourColumnHeader][YourRowFie
ld]
**Step 2: Create Rank Measure for Sorting**
Column Sort Rank =
RANKX(
ALLSELECTED('YourTable'),
[Total CTR],
,
DESC,
DENSE
)[YourColumnHeader]**Step 3: Apply the Sort**
1. Click on your `YourColumnHeader` field in the Fields pane
2. Go to `Column tools` tab > `Sort by column` > Select `Column Sort Rank`
**Result:**
Now columns will automatically sort from Highest Total to Lowest Total: CB | TQ | QW | AS | ER | YW
When new columns are added in future, they will also sort automatically based on Total row value.
**Important Notes:**
1. Replace `YourTable` with your table name
2. Replace `YourColumnHeader` with OD, YW, CB etc field
3. Replace `YourRowField` with LHR-PAR, PAR-LHR field
4. Turn OFF "Show items with no data" for Column field
This works because `RANKX` recalculates on every filter change, so when date filter changes, column order will also change.
Let me know if you need help with the exact field names. Happy to help!
We wanted to follow up to check if you’ve had an opportunity to review the previous responses. If you require further assistance, please don’t hesitate to let us know.
Hi SUMESHKUMAR22
Have you had a chance to look through the responses shared earlier? If anything is still unclear, we’ll be happy to provide additional support.
