Forum Discussion

Daniel48's avatar
Daniel48
Helper I
2 years ago
Solved

Cross Comparison Between Tables

Hello 

 

 

I am trying to do line by line comparison of categories between tables 

 

Table 1

 

Category   budget       

ABC0001   Orginal           11.00

ABC0001   Updated         10.50

 

 

Table 2

 

Category     Actual 

ABC0001     10.00

 

I have tried doing a lookup and because there are two line with the same category i get errors. I am trying to pull the 10 into the first table so I can do variances such as 11-10 for orginal and 11-10.50 for updated or is there a better way to row compairson 

 

Thank you in advance

 

 

Need Help
  • PhilipTreacy's avatar
    PhilipTreacy
    2 years ago

    Hi Daniel48 

     

    Download example PBIX file with the example below

     

    If you enter both tables into PBI, it should create a 1 to many relationship between the tables, based on the Category:

     

     

    If you then write this measure 

     

     

    Variance = MAX('TableA'[Value]) - MAX('TableB'[Actual])

     

     

    and put that into a table visual, you'll get this

     

     

    Regards

     

    Phil

     

2 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    2 years ago

    Hi Daniel48 

     

    Download example PBIX file with the example below

     

    If you enter both tables into PBI, it should create a 1 to many relationship between the tables, based on the Category:

     

     

    If you then write this measure 

     

     

    Variance = MAX('TableA'[Value]) - MAX('TableB'[Actual])

     

     

    and put that into a table visual, you'll get this

     

     

    Regards

     

    Phil