add different columns from different tables without using sum function

[Anonymous]

hi Team,

I have 2 different tables

 Table1

ID	Jun:A	Jul:A	Aug:A
123	5,643	9,876	5,820
456	7,639	1,543	777
789	9,875	567	9,876

Table2

ID	Jun:F	Jul:F	Aug:F
123	3,927	6,762	9,568
456	6,926	8,899	4,826
789	8,066	8,188	6,823

I want to add 3 columns to 2nd table whose formula will be:-  

Jun:A+Jul:F+Aug:F

Jun:A+Jul:A+Aug:F

Jun:A+Jul:A+Aug:A

When i am trying using -:Table1[Jun:A]+SUM(Table2[Jul:F]+SUM(Table2[Aug:F], I am not getting the result.

And without using SUM function the table2 is not being referenced.

Please help urgently.

[v-shex-msft]

Hi @Anonymous,

I'd like to suggest you unpivot these column to attribute and value, then you can simply use calculate with attribute equal to specific value to get result.

Unpivot Data Using Excel Power Query

Measure =
VAR currID =
    MAX ( Table1[ID] )
VAR merged =
    UNION ( ALLSELECTED ( Table1 ), ALLSELECTED ( Table2 ) )
VAR attrList = { "Jun:A", "Jul:F", "Aug:F" }
RETURN
    SUMX ( FILTER ( merged, [ID] = currID && [Attribute] IN attrList ), [Value] )

Regards,

Xiaoxin Sheng

[Stachu]

if it's calculated column in Table2 this should work

RELATED(Table1[Jun:A])+Table2[Jul:F]+Table2[Aug:F]

in Table1 this should work

Table1[Jun:A])+RELATED(Table2[Jul:F])+RELATED(Table2[Aug:F])