cancel
Showing results for
Did you mean:
Helper II

## How to add sum of two columns on a different table

Hello all,

Please help. I have 2 tables.  I need the sum of DEM and DET on Table 1 as one column in Table 2.

Table 1 data

 BL No Container Load Discharge DCHF SNTC RCVE DET DEM BL0001 CONT0001 PORT A PORT D 6/1/2020 4:12 6/8/2020 15:12 3 BL0001 CONT0001 PORT A PORT D 6/5/2020 10:03 4 BL0002 CONT0002 PORT A PORT D 6/1/2020 4:12 6/5/2020 11:56 1 BL0002 CONT0002 PORT A PORT D 6/4/2020 15:47 3 BL0003 CONT0003 PORT A PORT D 6/1/2020 4:12 6/10/2020 10:46 5 BL0003 CONT0003 PORT A PORT D 6/5/2020 13:50 4

Table 2 data

 BL Number Container OBHUS IBHUS IBLOLO BL0001 CONT0001 250.00 375 565 BL0002 CONT0002 250.00 375 565 BL0003 CONT0003 250.00 375 565

Result view for Table 2  should be as below.

 BL Number Container OBHUS IBHUS IBLOLO DET/DEM BL0001 CONT0001 250.00 375 565 7 BL0002 CONT0002 250.00 375 565 4 BL0003 CONT0003 250.00 375 565 9

5 REPLIES 5
Community Support

Hi @dnsia

Your two measures DET & DEM, are calculated by the specific logic, so you need to provide the detailed formula of them. Otherwise, we cant combine the two measure into Table2 without knowing the DAX expressions.

Best Regards,

Community Support Team _ Tang

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

Helper II

Here it is.

DEM =
var __bilno = [BL_No]
var __sntc =
MAXX( FILTER( 'EMS Destination' , 'EMS Destination'[BL_No] = __bilno ) , 'EMS Destination'[SNTC])
var __dchf =
MAXX( FILTER( 'EMS Destination' , 'EMS Destination'[BL_No] = __bilno ) , 'EMS Destination'[DCHF])
return
IF( 'EMS Destination'[SNTC] <> BLANK() , DATEDIFF( __dchf, __sntc , DAY ) )

Det =
var __bilno = 'EMS Destination'[BL_No]
var __rcve =
MAXX( FILTER( 'EMS Destination', 'EMS Destination'[BL_No] = __bilno ) , 'EMS Destination'[SNTC])
var __dchf =
MAXX( FILTER( 'EMS Destination' , 'EMS Destination'[BL_No] = __bilno ) , 'EMS Destination'[RCVE])
return
IF( 'EMS Destination'[RCVE] <> BLANK() , DATEDIFF( __rcve, __dchf , DAY ) )

Community Support

Hi @dnsia

However, according to the grammar of your two DAX expressions, they should be DAX Column

Anyway, create the measure:

``DET/DEM = CALCULATE(SUM('EMS Destination'[DEM Col])+SUM('EMS Destination'[Det Col]),ALLEXCEPT('EMS Destination','EMS Destination'[BL_No],'EMS Destination'[Container]))``

result:

See sample file attached bellow.

Best Regards,

Community Support Team _ Tang

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

Super User

@dnsia

You can merge the two tables and choose Aggregate when you expand the merged column then select DEM and DET. Add the resulting columns to get the total. Please find attached the completed file below my signature.

Did I answer your question? Mark my post as a solution! and hit thumbs up
Helper II

This is a great resolution if Det and Dem columns are included in the original data set.

Apologies, I forgot to mention Det and Dem columns are from measures and are not available in power query.

Announcements