Forum Discussion
Paginated Report - Calculated Field
I'm working with a paginated report, have a calculated field that displays encumbrances. I need to create a total for this column based on the following formula (Beginning Balance - YTD Expenses + YTD Receipts), when I do Sum(BegBal-YTDExpenses + YTD Receipts), it is only totaling YTD Expenses + YTD Receipts). Any insight would be appreciated. (All of these values are currently being displayed on the current report)
Hi Bernice,
Thanks for the additional details.Since the Unexpended Balance is calculated as Beginning Balance − YTD Expenses + YTD Receipts, I recommend checking the aggregation scope of the total expression and how BegBal is represented in the underlying dataset.
According to the Microsoft documentation, Sum aggregates non-null numeric values within the specified scope. If no scope is specified, it uses the current scope, which depends on where the expression is placed in the report. The documentation also explains that the scope of an expression affects which data is included in an aggregate calculation.
Could you check whether BegBal is repeated across multiple dataset rows for each fund type, or whether it appears only once? Also, please confirm the row group associated with the fund type and where the total expression is placed. This information will help determine the appropriate aggregation for the Unexpended Balance and the resulting End Balance.
For more details, please refer to the below Official Microsoft Documentation:
• Sum function in a paginated report - Microsoft Report Builder & Power BI Report Builder | Microsoft Learn
• Expression scope for totals, aggregates, and built-in collections in a paginated report - Microsoft Report Builder & Power BI Report Builder | Microsoft LearnI hope this helps. Please feel free to reach out if you have any further questions.
Thank you.
5 Replies
jubinsoniSkilled Sharer
Hi Bernice,
When a total of (Beginning Balance - YTD Expenses + YTD Receipts) comes out as if BegBal were missing, the usual cause in paginated reports is the grain of BegBal rather than the formula.
dataset rows are at transaction/line level BegBal is an account-level value -> either (a) repeated on every line -> Sum() counts it N times (too high), or (b) only on one line / NULL elsewhere -> the textbox shows it via First(), but the total row adds the dataset rows, not what is displayedSince your detail rows display correctly, the total needs to add one BegBal per account and the YTD amounts per line. Assuming a row group named "Account" (use your group name, case-sensitive, in quotes):
=Sum(Max(Fields!BegBal.Value, "Account")) - Sum(Fields!YTDExpenses.Value) + Sum(Fields!YTDReceipts.Value)The nested Sum(Max(..., "Account")) takes one balance per account group and then adds those up in the total row's scope. Put this in the total row outside the Account group (the tablix footer or a parent group's footer).
If any of the three can be NULL, guard it so a missing value counts as 0:
=Sum(Max(IIf(IsNothing(Fields!BegBal.Value), 0, Fields!BegBal.Value), "Account")) - Sum(IIf(IsNothing(Fields!YTDExpenses.Value), 0, Fields!YTDExpenses.Value)) + Sum(IIf(IsNothing(Fields!YTDReceipts.Value), 0, Fields!YTDReceipts.Value))The most robust option, if you can change the dataset query, is to return BegBal only once per account (or as a separate account-level dataset). Then a plain Sum of the encumbrance expression works everywhere, including grand totals and exports.
So my overall approach would be:
check: is BegBal repeated per line or only on one line? -> total = Sum(Max(BegBal, "Account")) - Sum(YTDExpenses) + Sum(YTDReceipts) -> place it outside the Account group scope; null-guard if needed -> long term: return BegBal at account grain from the queryAI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.
A BegBal is displayed once for every fund type, i.e General Fund, State Highway. Each fund type also calculates MTD Receipts (sum of receipts during that calendar month), YTD Receipts (sum of receipts for the year for given fund), MTD Expenses (sum of expenses during that calendar month), YTD Expenses (sum of expenses for the year for given fund), Unexpended Bal (calculated field - BegBal-YTDExpenses+YTDReceipts), Outstanding Encumbrance - calculated field - single value based on fund type), and EndBal (calculation - Unexpended Bal - Outstanding Encumbrance). Unexpended Balance is the only calculation that is not calculating properly. But it also is throwing off the End Bal but the calculation is working based on the data that is being displayed.
v-abhinavmuCommunity Support
Hi Bernice,
Thanks for the additional details.Since the Unexpended Balance is calculated as Beginning Balance − YTD Expenses + YTD Receipts, I recommend checking the aggregation scope of the total expression and how BegBal is represented in the underlying dataset.
According to the Microsoft documentation, Sum aggregates non-null numeric values within the specified scope. If no scope is specified, it uses the current scope, which depends on where the expression is placed in the report. The documentation also explains that the scope of an expression affects which data is included in an aggregate calculation.
Could you check whether BegBal is repeated across multiple dataset rows for each fund type, or whether it appears only once? Also, please confirm the row group associated with the fund type and where the total expression is placed. This information will help determine the appropriate aggregation for the Unexpended Balance and the resulting End Balance.
For more details, please refer to the below Official Microsoft Documentation:
• Sum function in a paginated report - Microsoft Report Builder & Power BI Report Builder | Microsoft Learn
• Expression scope for totals, aggregates, and built-in collections in a paginated report - Microsoft Report Builder & Power BI Report Builder | Microsoft LearnI hope this helps. Please feel free to reach out if you have any further questions.
Thank you.
- v-abhinavmu
Hi Bernice,
Thanks for reaching out to the Microsoft Fabric Community forum.You could try applying Sum to the complete calculation:
=Sum(Fields!BegBal.Value - Fields!YTDExpenses.Value + Fields!YTDReceipts.Value)Replace the field names with the actual dataset field names in your report.
According to the Sum function documentation, the function aggregates the result of the specified expression. If the total still doesn't match the expected value, check the scope in which the expression is evaluated. By default, Sum uses the current scope, which depends on the expression's location and, for a tablix cell, its row and column groups.
Could you clarify whether the calculated encumbrance is defined as a dataset field or as an expression in a report textbox? This would help determine the appropriate expression for your report.
For more details, please refer to the below Official Microsoft Documentation:
Expressions in Power BI Report Builder - Power BI | Microsoft Learn
I hope this helps. Please feel free to reach out if you have any further questions.
Thank you.
