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
Helper 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".


    • galaamri's avatar
      galaamri
      Helper I
      3 years ago

      Then there is other way to make this relationship works by creating a reference table (to just have the unique ticket numbers). to do this:

      Go to Power Query (Transform data), home, manage, then choose referece. you are creating a reference table1 based on the ticket number. then remove duplicate. then do the same for table2. 

      Connect the reference table1 and table1 (This should have one to many relationship), and the mesaure would work. 

      Regards

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Unfortunately it is still many to many relationship (I assume due to Table 1 will have ticket numbers table 2 doesn't have, and vice versa). But I am not entirely sure of the cause. But I do really appreciate the help.