Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello,
Below is a high-level overview of my issue.
I have three tables:
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).
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!
Solved! Go to 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
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
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
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
Hi @PawelK0296 please try this measure
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
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
24 | |
10 | |
6 | |
6 | |
6 |
User | Count |
---|---|
29 | |
11 | |
11 | |
10 | |
6 |