Adding the Value of Two Textboxes in Separate Tables

[PeteSil1020]

Hello,

This issue has been driving me crazy, so if someone is able to come up with a solution I will be extremely thankful.

To give an overview of the issue, I am attempting to sum the values of two textboxes together, but that are in two different tables, and are using two different datasets. So simply doing ReportItems!Textbox10.Value + ReportItems!Textbox20.Value won't work.

Now diving into what these textboxes are made up of, this is the expression for Textbox10:

=SUM(
IIF(
DateAdd("m", 1, Parameters!Quarter.Value) = Fields!Exit_Date.Value,
                     Lookup(
                    Fields!Investment.Value,
                    Fields!Investment.Value,
                    Fields!Recallable_Amount.Value,
                    "SumInvestmentsMeasures"
                      ),
              0
          )
)

Textbox 20:

=SUM(
IIF(
DateAdd("m", 1, Parameters!Quarter.Value) = Fields!estimated_date.Value,
Lookup(
                           Fields!deal_name.Value,
                            Fields!deal_name.Value,
                             Fields!Discretionary_Reserves.Value,
                             "InvestmentReserves"
                    ) / 1000000,
             0
        )
)

If someone has an idea of how I can get the values of these two textboxs to be aggegated, it would be a saving grace.

[Anonymous · Accepted solution]

Hi @PeteSil1020 ,

Create Custom Code Functions: You can write custom functions to aggregate your values.

Modify Your Textbox Expressions: Use the function within your existing expressions for Textbox10 and Textbox20 to add their values to the total. For example, for Textbox10, modify the expression.

=Code.AddToTotal(SUM(IIF(DateAdd("m", 1, Parameters!Quarter.Value) = Fields!Exit_Date.Value, Lookup(Fields!Investment.Value, Fields!Investment.Value, Fields!Recallable_Amount.Value, "SumInvestmentsMeasures"), 0)))

 To display the aggregated value, you can create another textbox and set its expression to . This textbox will display the sum of the values from Textbox10 and Textbox20.=Code.GetTotal().

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.