Forum Discussion
Exclude data from Subtotals in Power BI Matrix (But Keep Row Values)
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!
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
5 Replies
- maruthisp
Super User
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
- techies
Hi PawelK0296 please try this measure
VAR IsSub = NOT HASONEVALUE('Values'[FS Account])RETURNIF(IsSub,CALCULATE(SUM('Values'[Value]),FILTER(ALL('Values'[FS Account]),LEFT('Values'[FS Account], 1) <> "S")),SUM('Values'[Value])) 
v-sdhruvCommunity Support
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
