Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Comparing Difference between two columns in two different tables

Hello, I have two tables that I am needing to compare and find the difference between.  Specifically, the tables have one column that should have matching values (ticket numbers), if the ticket nu...
  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi Anonymous 

    Please create a dimension table that contains all ticket numbers. This can be created in power query or can be a calculated table using the following dax

    Tickets =
    DISTINCT (
    UNION (
    ALLNOBLANKROW ( Table1[Ticket Num] ),
    ALLNOBLANKROW ( Table2[Ticket Num] )
    )
    )
    then you apply the following measure 

    Difference =
    COALESCE (
    SUMX (
    VALUES ( Tickets[Ticket Num] ),
    CALCULATE (
    VAR Val1 =
    SUM ( Table1[Value] )
    VAR Val2 =
    SUM ( Table1[Value] )
    RETURN
    IF ( Val1 <> BLANK () && Val2 <> BLANK (), Val2 - Val1 )
    )
    ),
    "No Match"
    )

TaylorPBI21's avatar
TaylorPBI21
Resolver I
3 years ago

Hi Anonymous ,

 

This is how you get the difference...

Difference = SUM('Table 1'[Volume]) - SUM('Table 2'[Volume])

 

Make sure Ticket Number is type Text or it won't work in your graph properly.

 

Many Thanks,

 

Taylor😎