Forum Discussion

dejanzoric's avatar
dejanzoric
Frequent Visitor
3 years ago
Solved

Creating the exposure difference in two tables

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

General Comment
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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