topic Re: Exclude data from Subtotals in Power BI Matrix (But Keep Row Values) in DAX Commands and Tips

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

PawelK0296 — Tue, 10 Jun 2025 17:27:05 GMT

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!

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

maruthisp — Wed, 11 Jun 2025 01:52:33 GMT

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

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

techies — Wed, 11 Jun 2025 20:14:52 GMT

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])

)

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

v-sdhruv — Mon, 16 Jun 2025 07:26:18 GMT

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

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

v-sdhruv — Thu, 19 Jun 2025 08:02:01 GMT

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

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

v-sdhruv — Mon, 23 Jun 2025 12:13:26 GMT