Forum Discussion

Joshva's avatar
Joshva
New Member
2 years ago
Solved

How to compare/calculate between two columns from different tables with missing values?

My goal is to compare two columns that are supposed to be the same. Due to some missing/differing data, this is harder to work with. The structure is simple: ID, amount. I want a measure that expresses the difference for all ID numbers.

 

Right now I have the following

mymeasure =
var number = MAX(table1[ID])
var table1value = CALCULATE(sum(table1[amount]), table1[ID] = number)
var table2value= CALCULATE(sum(table2[amount]), table2[ID] = number))
RETURN table1value -table2value

This results into many values being not calculated, because there are many missing ID numbers (in both tables). I want to make a simple table visualization with ID, amount1, amount2, measure (which expresses the difference).
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Joshva ,

     

    bjertf , thanks for your concern about this case. I tried to create a sample data myself based on the user's requirement. Please check if there is anything that can be improved. Here is my solution:

     

    1.Create simple data:

     

     

    2.In Power Query Editor, append the two tables into a single table:

     

     

    The relationship is as follows:

     

     

    3.Create a new measure:

     

    Measure = 
VAR _sum1 = CALCULATE(SUM(A[Amount]),FILTER(ALL(A),'A'[ID] = MAX('Append1'[ID])))
VAR _sum2 = CALCULATE(SUM(B[Amount]),FILTER(ALL(B),'B'[ID] = MAX('Append1'[ID])))
RETURN
_sum1 - _sum2

     

    4.The final result is as follows:

     

     

    Best Regards,
    Zhu
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

     

3 Replies

  • bjertf's avatar
    bjertf
    Regular Visitor
    2 years ago

    I am not sure whether what you want is possible without making another table?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Joshva ,

     

    bjertf , thanks for your concern about this case. I tried to create a sample data myself based on the user's requirement. Please check if there is anything that can be improved. Here is my solution:

     

    1.Create simple data:

     

     

    2.In Power Query Editor, append the two tables into a single table:

     

     

    The relationship is as follows:

     

     

    3.Create a new measure:

     

    Measure = 
VAR _sum1 = CALCULATE(SUM(A[Amount]),FILTER(ALL(A),'A'[ID] = MAX('Append1'[ID])))
VAR _sum2 = CALCULATE(SUM(B[Amount]),FILTER(ALL(B),'B'[ID] = MAX('Append1'[ID])))
RETURN
_sum1 - _sum2

     

    4.The final result is as follows:

     

     

    Best Regards,
    Zhu
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

     

    • Joshva's avatar
      Joshva
      New Member
      2 years ago

      Didn't completely work for me, but you were great inspiration for my solution, thanks!