cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dnsia
Helper II
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
v-xiaotang
Community Support
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.

 

 

 

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 ) )


v-xiaotang
Community Support
Community Support

Hi @dnsia 

Thanks for your reply.

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:

v-xiaotang_0-1622022077599.png

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.

Fowmy
Super User
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.

Fowmy_0-1621755285392.png

 

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


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

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. 

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors