Creating the exposure difference in two tables

[dejanzoric]

Hi, I have encountered a problem that I do not know how to solve.

Description:

I have two tables: Table 1 and Table 2 (they are large databases. Both tables have a million rows each)

Tables have a hard join link: ID

Issue

The ID in the tables is repeated multiple times

The columns in the tables are different so I can't simply merge them. Only their ID and amount are unique.

EXAMPLE:

Table 1

ID	Amount
11000	1.000
11000	200
11001	450
11002	730

Table 2

ID	Amount
11000	1.000
11001	450
11004	300
11005	1.250

Result:

ID	Table 1	Table 2	Difference
11000	1.200	200	1.000
11001	450	100	350
11002	730		730
11004		300	-300
11005		1.250	-1.250

I tried to do as follows:

1. I have created two pivot tables Via DAX Query.
2. I summarized the amounts to the ID level. So now I have one ID and the exposure sum on that ID for each table separately.
3. In the model I created a 1 to 1 relationship by ID
4. In the Report section, I created a table similar to the Result, but I don't get the desired effect

The pivot makes a comparison for me, but it only gives me the ID from table 1

ID	Table 1	Table 2	Difference
11000	1.200	200	1.000
11001	450	100	350
11002	730		730

Thanks in advance for your help,

Dejan

[Anonymous · Accepted solution]

Hi @dejanzoric ,

Base on your provided data, what's expected result? Is the below table your expected result? If yes, how do you get these values especially the values with red line or circle?

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

1. Create a dimension table as below

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

2. Create the measures as below

Amount1 = 
VAR _ids =
    SELECTEDVALUE ( 'Table'[ID] )
RETURN
    CALCULATE (
        SUM ( 'Table 1'[Amount] ),
        FILTER ( 'Table 1', 'Table 1'[ID] = _ids )
    )

Amount2 = 
VAR _ids =
    SELECTEDVALUE ( 'Table'[ID] )
RETURN
    CALCULATE (
        SUM ( 'Table 2'[Amount] ),
        FILTER ( 'Table 2', 'Table 2'[ID] = _ids )
    )

Difference = [Amount1]-[Amount2]

3. Create a table visual

Best Regards