Custom Subtotal Labels In a Matrix

[tom-lenzmeier]

Hello,

I have a matrix for sales where I want to have custom labels for subtotals. I have a business, a product group, and the product. I have created a hierarchy and all the amounts are as expected. However, I can't seem to figure out how to get the subtotal line to display the business unit instead of just "total." For example

Business Unit	Group	Sales
Unit A	Group 1	$100
	Group 2	$100
Unit A Total		$200
Unit B	Group 3	$100
	Group 4	$100
Unit B Total		$200
Total		$400

[v-nmadadi-msft]

Hi @tom-lenzmeier  ,
Thanks for reaching out to the Microsoft fabric community forum.

Try to create a hierarchy in your data,
Go to Model View.

Right click on Business Unit and click on create hierarchy

Next in the properties panel

add Product group and Product, them click on apply changes.
You would have create a business unit hierarchy with the above steps.

Include that in your matrix and use sales as the values within it.

Here is the output

Attaching the .pbix for reference.

I hope this information helps. Please do let us know if you have any further queries.
Thank you

[Ritaf1983 · Accepted solution]

Hi @tom-lenzmeier 

In a Power BI Matrix, you can only set one uniform label for subtotals . There’s no built-in way to display a different subtotal label for each group

If you need a different subtotal name per category, you can work around this by creating a “fake subtotal row” in your dimension table.

Steps:

Create a dimension table (e.g., Categories Matrix) with.

For example:

Bu (category)

Name (subcategory/product)

Extra rows for each category, such as “Total IT”, “Total Books”, “Total Clothes”.

Sort columns (Sort BU, SortName) to ensure the “Total …” row is always at the bottom of its group.

Create an inactive relationship between Categories Matrix[Name] and your fact table’s Name column.

Build a measure that:

Returns BLANK() if Bu is blank (avoids unwanted Matrix subtotal rows).

If Name contains “Total” → ignores the subcategory and sums at the category level.

Otherwise → activates the inactive relationship and sums at the subcategory level.

Sum for matrix :=
VAR IsTotalRow =
CONTAINSSTRING( UPPER( SELECTEDVALUE('Categories Matrix'[Name], "") ), "TOTAL" )

VAR Sum_ByBU =
CALCULATE(
SUM('Table'[Sales]),
REMOVEFILTERS('Categories Matrix'[Name]),
TREATAS( VALUES('Categories Matrix'[Bu]), 'Table'[Bu] )
)

VAR Sum_Detail_ByName =
CALCULATE(
SUM('Table'[Sales]),
USERELATIONSHIP('Table'[Name], 'Categories Matrix'[Name])
)

RETURN
IF(
ISBLANK( SELECTEDVALUE('Categories Matrix'[Bu]) ),
BLANK(),
IF( IsTotalRow, Sum_ByBU, Sum_Detail_ByName )
)

Now you can create a Matrix ( use the created table for rows of the matrix)
Note: You can’t bold the “Total …” label itself (it’s a regular row), but you can format the values using Conditional Formatting based on the IsTotalRow flag.

The pbix is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[MFelix]

Hi @tom-lenzmeier ,

You can customize the subtotal to be whatever value you want however that value is not dynamic you need to hardcode the value you need:

So you are not able to do this type of customization.
You can add a custom field but it will not be aligned with the first column because it would be placed on the values part of the table.