Sum 2 Table values

Question

I am attempting to sum two values across two tables. I have a pending amount calculated for both tables, and now need to see the sum of both calculated fields in a multi-row card. Table 1 gives me a dollar amount, table 2 gives me a dollar amount. I need the sum of the two amounts.

Table 1 calculated field:

Pending Tender (FTL-CS) = IF('ICS Que'[DeliveryDate]=BLANK(),BLANK(),'ICS Que'[TenderedAmount])

Table 2 Calculated field:

Pending Tender (FTL-AP) = IF('AP Que'[DeliveryDate]=BLANK(),BLANK(),'AP Que'[TenderedAmount])

Sum of both field attempt:

Pending Tender (FTL) = CALCULATE(SUM('AP Que'[Pending Tender (FTL-AP)]&SUM('ICS Que'[Pending Tender (FTL-CS)])))

I know it is in my syntax but I dont know how to fix it.

Marcus Marcus · Accepted Answer · 2018-04-23T17:34:47

What you have written is the following:

    Pending Tender (FTL) := 
CALCULATE(SUM('AP Que'[Pending Tender (FTL-AP)]&SUM('ICS Que'[Pending Tender (FTL-CS)])))

It should be written as the below, where the first SUM has a closing bracket, and there is a + instead of using & between the two SUM

Pending Tender (FTL):=
CALCULATE (
    SUM ( 'AP Que'[Pending Tender (FTL-AP)] )
        + SUM ( 'ICS Que'[Pending Tender (FTL-CS)] )
)

Or it could be rewritten as the below, since the calculate is redundant unless you are having a filter condition on it.

Pending Tender (FTL):=
SUM ( 'AP Que'[Pending Tender (FTL-AP)] )
    + SUM ( 'ICS Que'[Pending Tender (FTL-CS)] )

Finally as a recommendation, I would personally avoid using ( or ) in measure names, since they can be mistaken for opening and closing brackets.

Sum 2 Table values

1 Answers