Adding a second subtotal in a matrix

I actually ran into this exact issue once.

I needed to show the regular Quarter subtotal in a Matrix, but also an additional row under each quarter showing a cumulative (running total) for the Sales columns. At first, I tried using the built-in subtotals, but Power BI only allows one subtotal per level — so adding a second one isn’t possible natively.

Here’s how I solved it.

First, I turned off the default subtotals in the Matrix visual. That gave me full control.

Then I created a small disconnected helper table to simulate the different row types:

Row Type =
DATATABLE(
"Type", STRING,
{
{"Detail"},
{"Subtotal"},
{"Cumulative"}
}
)

This table is not related to anything in the model.

Next, I created a running total measure that calculates the cumulative value within each quarter.

For example, for Total Sales:

Total Sales RT Quarter =
CALCULATE(
[Total Sales],
FILTER(
ALL('Date'),
'Date'[Year] = MAX('Date'[Year]) &&
'Date'[Quarter] = MAX('Date'[Quarter]) &&
'Date'[Date] <= MAX('Date'[Date])
)
)

This makes the running total reset every quarter.

Then I created a dynamic measure to control what gets displayed in the Matrix:

Total Sales Matrix =
VAR RowType = SELECTEDVALUE('Row Type'[Type])
RETURN
SWITCH(
TRUE(),

-- Normal month values
RowType = "Detail" && ISINSCOPE('Date'[Month]),
[Total Sales],

-- Quarter subtotal
RowType = "Subtotal" && NOT ISINSCOPE('Date'[Month]),
CALCULATE([Total Sales], REMOVEFILTERS('Date'[Month])),

-- Running total within quarter
RowType = "Cumulative" && NOT ISINSCOPE('Date'[Month]),
[Total Sales RT Quarter],

BLANK()
)

Finally, I structured the Matrix rows like this:

Year

Quarter

Month

Row Type[Type]

And I made sure the built-in subtotals were turned off.

I hope it works 🙂

Hi SamuelH 

Try this approach:

Create a display table and relate that to your date table using a many-to-many single direction relationship.

Using Tabular Editor (external tool that needs to be installed separately), sort category column index column. Tabular Editor is necessary to force sort by multiple values the category column as Power BI's UI doesn't allow it. Save i Tabular Editor to apply the changes to the model

Create a measure similar to below:

Revenue - two subtotals = 
VAR _category =
    SELECTEDVALUE ( DisplayTable[Category] )
RETURN
    SWITCH (
        TRUE (),
        _category = "" && NOT ( ISINSCOPE ( Dates[Month and Year] ) ), BLANK (),
        _category = "Subtotal" && NOT ISINSCOPE ( Dates[Month and Year] ), [Total Revenue],
        _category = "Cumulative" && NOT ISINSCOPE ( Dates[Month and Year] ), CALCULATE ( [YTD Revenue], REMOVEFILTERS ( DisplayTable[Quarter] ) ),
        _category = "", [Total Revenue]
    )

In matrix, disable indention as well as the expand button of the row headers.  The blank rows below is where the DisplayTable[Category] = "" is. This cannot be hidden as doing so will hide the months as well.

Please see the attached pbix.