Exclude data from Subtotals in Power BI Matrix (But Keep Row Values)

[PawelK0296]

Hello,

Below is a high-level overview of my issue.

Data Model:

I have three tables:

• Table A: FS Account, Date
• Table B: Date, FS Account, Level 1, Level 2, ..., Level 5
• Table C: Date, FS Account, Value

Matrix Setup:

I'm using a matrix visual with a 5-level hierarchy (e.g., Level 1 → Level 2 →Level 3→Level 4→Level 5→ FS Account). Some levels contains statistical accounts, which always start with "S" (e.g., S1010).

What I want to achieve:

• ✅ Keep statistical accounts visible in the matrix rows.
• ❌ Exclude them from subtotals and totals at higher levels.

Example:

Level 1                  Level 2                FS Account                                  Value

Fixed Income	Gov Bonds	1010 - Treasury Bonds	100,000,000
		S1010 - Statistical Adjustment	5,000,000
		S1020 - Statistical Offset	3,000,000
	Subtotal Level 2		100,000,000 ← should exclude S1010 and S1020
	Gov Bonds 2	1011 - Treasury Bonds	100,000,000
		S1011 - Statistical Adjustment	5,000,000
		S1021 - Statistical Offset	3,000,000
	Subtotal Level 2		100,000,000 ← should exclude S1010 and S1020
Subtotal Level 1			200,000,000

Could you please let me know if it possible to achive that? I know i can exclude the statistical accounts but then that value are not visible. 

Thank you!

[v-sdhruv]

Hi @PawelK0296 ,
Just wanted to check if you had the opportunity to review the suggestion provided?
If the response has addressed your query, please accept it as a solution  so other members can easily find it.
Thank You

[v-sdhruv]

Hi @PawelK0296 ,
Just wanted to check if you had the opportunity to review the suggestion provided?
If the response has addressed your query, please accept it as a solution  so other members can easily find it.
Thank You

[v-sdhruv]

Hi @PawelK0296 ,
Just wanted to check if you had the opportunity to review the suggestion provided?
If the response has addressed your query, please accept it as a solution  so other members can easily find it.
Thank You

[techies]

Hi @PawelK0296 please try this measure

VAR IsSub = NOT HASONEVALUE('Values'[FS Account])

RETURN

IF(

    IsSub,

    CALCULATE(

        SUM('Values'[Value]),

        FILTER(

            ALL('Values'[FS Account]),

            LEFT('Values'[FS Account], 1) <> "S"

        )

    ),

    SUM('Values'[Value])

)

[maruthisp · Accepted solution]

Hi PawelK0296,

As per your original post, assuming you have value column is TableC[Value] and FS Account is in TableC[FS Account]:

Adjusted Value =
VAR IsTotal = HASONEVALUE('TableC'[FS Account]) = FALSE
RETURN
IF(
IsTotal,
CALCULATE(
SUM('TableC'[Value]),
NOT STARTSWITH('TableC'[FS Account], "S")
),
SUM('TableC'[Value])
)

Above DAX measure does below:
1.Detects if the current row is a total/subtotal.
2.Excludes statistical accounts (those starting with "S") from totals.
3.Includes all accounts in row-level values.

Please let me know if you have further questions.

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks! 

Best Regards, 

Maruthi 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X