How to fix total matrix row

[Elena_Kalina]

Hi Everyone!

I’m facing an issue where the 'Total' row in my matrix changes its position depending on the number of rows in the data. Since the data is dynamic, I can’t predict where it will appear. However, I need it to always stay at the bottom. I tried to solve this by setting a fixed row size for each row and calculating how many rows the table should have, then creating a measure to add blank rows if there are fewer rows than expected. Unfortunately, this didn’t work. Could you suggest another solution to this problem? I’d really appreciate your help.

[danextian]

Hi @Elena_Kalina 

If you're using the compact layout, the grand total is always frozen to the bottom of the visual.  Or are you using a total row that isn't a subtotal or grand total row?

[Elena_Kalina]

@danextianThank you for your attention to my issue. I don’t need to explain it in more detail—I’m specifically referring to the Grand Total row. The problem persists regardless of the matrix layout or state.

Why do I need to fix the Total row?
The client requires a table view with:

• A highlighted header

• A Total row

• No vertical column separators

When I set colors for the headers and Total row (while making grid lines blend with the table body), the light grid lines become visible against the dark background.

Since the header position is fixed, I can overlay a shape to mask the issue there. But this won’t work for the Total row—its position keeps changing unpredictably.

Why does it move?
The matrix displays metrics for each client’s managers. Client A might have 20 managers (filling the designated table space), while Client B has only 2 managers—causing the Total row to jump upward

[danextian]

The gridline still appearing against a dark background despite being disabled is a known bug. Try choosing a lighter background instead

Although the grand total can be frozen, it will appear just after the last row if there are only a few rows. It remains fixed only when the visual becomes scrollable - i.e., when there are more rows than can be displayed within the current visual size. There is no available option to change this.

[Elena_Kalina]

Yes, I understand this, but our designer continues to insist on a dark background. Therefore, I was forced to contact the forum for help

[v-karpurapud · Accepted solution]

Hi @Elena_Kalina 

We understand the design limitations you're experiencing. As @danextian  mentioned, the Grand Total row in a matrix visual stays at the bottom only if the visual is scrollable. If all rows fit in the visual, the total appears after the last row, which may vary depending on the data. There currently isn't a setting to always keep the Grand Total at the bottom, no matter the number of rows.

Regarding grid line visibility on dark backgrounds, this is a known issue. You might suggest that the designer use a lighter background or adjust the grid line transparency to improve contrast until the issue is resolved.

Regards,
Karpurapu D,
Microsoft Fabric Community Support Team.

[Elena_Kalina]

Also, if I could lock the Total row in place, I would be able to color that entire row consistently by placing a same-colored object behind the matrix

[DataNinja777]

Hi @Elena_Kalina ,

It’s a common challenge to find that a 'Total' row included in your source data doesn't stay fixed at the bottom of a matrix visual. Your attempt to solve this by creating blank rows was a creative one, but the core issue lies with how Power BI inherently sorts information. By default, the matrix sorts its row labels alphabetically, which means a 'Total' row will simply fall into place based on the letter 'T', rather than at the end where you expect it.

The most robust and recommended way to fix this is to define a custom sort order. You can achieve this by creating a special helper column whose only purpose is to tell Power BI the correct sequence for your rows. The 'Total' row would be assigned the highest value in this column, ensuring it always comes last. This can be done directly in your data model using a DAX calculated column. Navigate to the Data view in Power BI, select the relevant table, and create a new column using the following formula. Remember to replace 'YourTable'[CategoryColumn] with the name of your table and the column containing the "Total" label.

SortOrder = IF('YourTable'[CategoryColumn] = "Total", 2, 1)

This simple expression creates a SortOrder column, assigning a 2 to the 'Total' row and a 1 to all other rows. Alternatively, you could perform this step in the Power Query Editor by selecting "Transform data," choosing your table, and using the "Conditional Column" feature under the "Add Column" tab to create the same logic before the data is loaded into the model.

Once this helper column exists, you must instruct Power BI to use it. While still in the Data view, select the original category column that contains your row labels. A "Column tools" tab will appear in the top ribbon. Within this tab, click the "Sort by Column" button and choose your newly created SortOrder column from the dropdown menu. After applying this setting, your matrix visual will respect this new sequence, effectively locking the 'Total' row to the bottom when sorted in ascending order.

There is, however, an even simpler method if your 'Total' row is just a mathematical sum of the other rows. In this case, the best practice is to remove the 'Total' row from your source data completely. Then, select your matrix visual and go to the "Format your visual" pane. Here, you can expand the "Row subtotals" section, switch it on, and set its position to "Bottom." This leverages Power BI's native calculation engine, which is efficient and guarantees the total is always accurate and correctly placed.

As a final, though not recommended, alternative for a quick fix, you could modify the 'Total' label in your source data to something like z_Total. This forces it to the end of an alphabetical sort, but it comes at the cost of displaying an unprofessional label to your users. For a maintainable and professional report, using the "Sort by Column" feature is the superior technique, giving you complete control over the presentation.

Best regards,