Show values in a matrix from two different tables based on a special ID column in each table

[An0n]

I'm trying to show values in a matrix from two different tables based on an ID column in text format. This ID column does not match fully between the two tables and one ID can still have multiple rows attached to it.

Table 1

ID	Column 3	Column 4	Column 5	AMOUNT Table 1
AFG137B08	xyz	1277	US	4
BFD159B08	xyz	1233	EU	15
BFD159B08	abc	1245	DE	12
CCC096A22	def	1233	US	9
XTU544L51	xyz	1245	EU	6

Table 2

ID	Column 3	Column 4	Column 5	AMOUNT Table 2
AFG137B08	xyz	1277	US	7
BFD159B08	xyz	1233	EU	20
CCC096A22	abc	1245	DE	3
BVN213H78	def	1233	US	14
AFG137B08	bte	1245	US	13

Desired Matrix in visuals

ID	AMOUNT Table 1	AMOUNT Table 2	Difference AMOUNT Table 1 and Table 2
AFG137B08	4	20	-16
BFD159B08	27	20	7
CCC096A22	9	3	6
BVN213H78	0	14	-14
XTU544L51	6	0	6

Hope this helps to describe the problem.

Thanks in advance!

[v-yiruan-msft · Accepted solution]

Hi @An0n ,

I create a sample pbix file(see the attachment), please check if that is what you want. 

1. Create a dimension table with IDs which is from both Table 1 and Table 2

IDs = DISTINCT ( UNION ( VALUES ( 'Table 1'[ID] ), VALUES ( 'Table 2'[ID] ) ) )

2. Create the measures as below to get the sum of amount in Table 1 and Table 2, the difference of amount

Amount_t1 = 
VAR _selid =
    SELECTEDVALUE ( 'IDs'[ID] )
RETURN
    CALCULATE (
        SUM ( 'Table 1'[AMOUNT Table 1] ),
        FILTER ( 'Table 1', 'Table 1'[ID] = _selid )
    ) + 0

Amount_t2 = 
VAR _selid =
    SELECTEDVALUE ( 'IDs'[ID] )
RETURN
    CALCULATE (
        SUM ( 'Table 2'[AMOUNT Table 2] ),
        FILTER ( 'Table 2', 'Table 2'[ID] = _selid )
    ) + 0

Difference of Amount = [Amount_t1]-[Amount_t2]

3. Create a table visual as below screenshot

Best Regards

[An0n]

Thank you @v-yiruan-msft this solution worked for me.

May I have a short follow-up request?

How do I show the correct column totals? They currrently show zero on my end, as also in your example.

Appreciate your help, thanks in advance!

[v-yiruan-msft]

Hi @An0n ,

I updated the sample pbix file(see the attachment), please check if that is what you want.

1. Create another two measures as below to replace the measure [Amount_t1] and [Amount_t2] onto the visual

Measure = SUMX(VALUES('IDs'[ID]),[Amount_t1])

Measure 2 = SUMX(VALUES('IDs'[ID]),[Amount_t2])

2. Update the formula of measure [Difference of Amount] as below

Difference of Amount = [Measure]-[Measure 2]

Best Regards

[FreemanZ]

hi @An0n 

one way of implementation is to create sum tables each then you can join them and then calculate. Like:

SumTable1 =

ADDCOLUMNS

    VALUES(Table1[ID],

    "Amount1",

    CALCULATE(SUM(Table1[Amount]))

)