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"
    )

galaamri's avatar
galaamri
Icon for Helper I rankHelper I
3 years ago

Hi Anonymous 

First we need to create a relationship between the two tables, common Column is Tikcket number.

Second, we match the values or ticket numbers by creating calculated column so 

m4 =
     var _LookMtaches =
     LOOKUPVALUE(Table1[Ticket Num],Table2[Ticket Num],Table1[Ticket Num])
     Return
     _LookMtaches
Third we Calculate the difference = 
M3 =
      CALCULATE(SUM(Table1[Volume])-SUM(Table2[Volume]))
Finally Create your table so
M4, Volume from table 1, volume from table 2, M3.
Please note: 1- you might have empty columns so Drag M4 to filter panel and choose is not blank. 
                     2- When drag M4 to the summary table choose "don't summarize" 
I hope this helps and please check the answer if it is right
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hey Galaamri, thank you for being willing to help. I do believe this would be the solution if I could get the relationship to work. 

When creating m4, I get the error "The column 'table 2 [ticket number]' either doesn't exist or doesn't have a relationship to any table available in the current context".

Extra info in case any of these help with solving the errors:
-I am attempting to create the calculated column column inside of table 1
-The ticket numbers from table 1 and table 2 will not match, a lot will but a lot will not. Some also include characters instead of just numbers. Both ticket columns are data type "text".
-When creating a relationship between the two before attempting to create m4, it makes a "many to many" relationship".