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 hav...
  • 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