cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

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.

1 ACCEPTED SOLUTION
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

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.

Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User

hi @An0n

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

SumTable1 =
VALUES(Table1[ID],
"Amount1",
CALCULATE(SUM(Table1[Amount]))
)

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors