Structured Financial Report-Subtotal - Power BI

[Felix_nsama]

I am trying to build a structured Financial Report with Traditional Struture ,so i have created a Dim -Report table with Report ID as key and includes sort order column )which is sharing a relationship with Chart of Accounts ReportID (lookup) . I written a measure using selectvalue and am able to get the figures by Expense code but no subtotals . What do I need to do to get sub-totals

[Juan-Power-bi · Accepted solution]

The subtotal problem in financial reports is a classic Power BI challenge — SELECTEDVALUE only works at the detail row level, so when the visual renders the subtotal row, there's no single value selected and the measure returns blank.
The fix is to use ISINSCOPE to detect which level of the hierarchy you're at and calculate accordingly:
daxFinancial Amount =
IF(
ISINSCOPE('ChartOfAccounts'[ExpenseCode]),
-- Detail level: return the individual account amount
SELECTEDVALUE(...your existing logic...),
-- Subtotal/total level: sum all amounts in the current group
CALCULATE(SUM('FactTable'[Amount]))
)
The key insight is that at the subtotal row, ISINSCOPE('ChartOfAccounts'[ExpenseCode]) returns FALSE because you're no longer scoped to an individual expense code — you're at the Report Category level. So you can branch the logic: detail rows get your SELECTEDVALUE logic, and subtotal rows get a regular SUM that aggregates everything in the current filter context.
If your report structure has multiple levels (e.g., Category → Subcategory → Account), you'd nest multiple ISINSCOPE checks:
daxFinancial Amount =
IF(
ISINSCOPE('ChartOfAccounts'[ExpenseCode]),
[Detail Level Measure],
IF(
ISINSCOPE('DimReport'[SubCategory]),
[SubCategory Subtotal],
[Grand Total]
)
)
If some subtotals need to be calculated differently (e.g., Gross Profit = Revenue - Expenses rather than just summing children), you'd handle those specific rows with additional SELECTEDVALUE('DimReport'[ReportID]) checks inside the subtotal branch.

[v-pgoloju]

Hi @Felix_nsama,

Thank you for reaching out to the Microsoft Fabric Forum Community, and special thanks to @danextian, @Praful_Potphode  and @Juan-Power-bi  for prompt and helpful responses.

Just following up to see if the Response provided by community members were helpful in addressing the issue. if the issue still persists Feel free to reach out if you need any further clarification or assistance.

Best regards,
Prasanna Kumar

[v-kpoloju-msft]

Hi @Felix_nsama, 

Just wanted to follow up. If the shared guidance worked for you, that’s wonderful hopefully it also helps others looking for similar answers. If there’s anything else you'd like to explore or clarify, don’t hesitate to reach out.

Thank you.

[danextian · Accepted solution]

Power BI matrix follows a hierarchy so a combination of conditional measures and a disconnected is normally needed to get the desired layout. The actual solution depends on the data. Below is an example result using such an approach. Notice that Gross Profit, Budget and Variance although expanded do not have anything under them.

[Praful_Potphode]

Hi @Felix_nsama ,

Can you provide more information on input/output/dax code?

Thanks and Regards,

Praful

[Juan-Power-bi · Accepted solution]

The subtotal problem in financial reports is a classic Power BI challenge — SELECTEDVALUE only works at the detail row level, so when the visual renders the subtotal row, there's no single value selected and the measure returns blank.
The fix is to use ISINSCOPE to detect which level of the hierarchy you're at and calculate accordingly:
daxFinancial Amount =
IF(
ISINSCOPE('ChartOfAccounts'[ExpenseCode]),
-- Detail level: return the individual account amount
SELECTEDVALUE(...your existing logic...),
-- Subtotal/total level: sum all amounts in the current group
CALCULATE(SUM('FactTable'[Amount]))
)
The key insight is that at the subtotal row, ISINSCOPE('ChartOfAccounts'[ExpenseCode]) returns FALSE because you're no longer scoped to an individual expense code — you're at the Report Category level. So you can branch the logic: detail rows get your SELECTEDVALUE logic, and subtotal rows get a regular SUM that aggregates everything in the current filter context.
If your report structure has multiple levels (e.g., Category → Subcategory → Account), you'd nest multiple ISINSCOPE checks:
daxFinancial Amount =
IF(
ISINSCOPE('ChartOfAccounts'[ExpenseCode]),
[Detail Level Measure],
IF(
ISINSCOPE('DimReport'[SubCategory]),
[SubCategory Subtotal],
[Grand Total]
)
)
If some subtotals need to be calculated differently (e.g., Gross Profit = Revenue - Expenses rather than just summing children), you'd handle those specific rows with additional SELECTEDVALUE('DimReport'[ReportID]) checks inside the subtotal branch.