Adding two different columns from two different tables and grouping

Question

Hi Guys,&nbsp;I have two different tables SalesLineQTY and ScheduledQty.they are joined to common DATE table in a model.I want to add ScheduleNeedQty and QtyOnSalesLine columns which will display the sum grouped by Item No_.I tried using sum and simple "+"&nbsp; but not getting required output as column from other table does not populate in DAX editor.Bit confused with&nbsp; which expression to be used with&nbsp;SUMMARIZE function .&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Any suggestion/help would be appreciated.&nbsp;

v-yuta-msft · Answer

Hi&nbsp;DAX_Learner,
&nbsp;
You may try dax formula below:
&nbsp;
Temp_Table =
SUMMARIZE (
    SalesLineQTY,
    SalesLineQTY[No_],
    "QtyOnSalesLine", SUM ( SalesLineQTY[QtyOnSalesLine] ),
    "ScheduleNeedQty", SUM ( ScheduledQty[ScheduleNeedQty] )
)

Regards,
Jimmy Tao

anonymous · Answer

Thank you for your suggestion&nbsp;v-yuta-msftActually I want to add&nbsp;ScheduleNeedQty and QtyOnSalesLine columns (which belongs to different tables) and display the output as per Item_No grouping.

v-yuta-msft · Answer

HI&nbsp;DAX_Learner,
&nbsp;
You can use ADDCOLUMNS() instead, please refer to: https://msdn.microsoft.com/en-us/query-bi/dax/addcolumns-function-dax
&nbsp;
Regards,
Jimmy Tao

anonymous · Answer

Addcoulmn&nbsp; does not&nbsp; work.Below is the BI model created.All the tables are connected using NO_ key (Removed Earlier relationship with General date table)Need to add&nbsp; different columns from highlighetd tables to get values as per Item no.&nbsp;&nbsp;

Forum Discussion

Adding two different columns from two different tables and grouping

4 Replies

Related content

Page Level filter for different groups

Different schema into different destination

Calculation Groups - Difference measures

Difference between different Staging lakehouses

Matrix data presentation for different groups