Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Daniel48
Helper I
Helper I

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

 

 

1 ACCEPTED SOLUTION
PhilipTreacy
Super User
Super User

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:

 

relationship.png

 

If you then write this measure 

 

 

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

 

 

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

 

variance.png

 

Regards

 

Phil

 



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

2 REPLIES 2
Daniel48
Helper I
Helper I

Thank you it worked !

PhilipTreacy
Super User
Super User

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:

 

relationship.png

 

If you then write this measure 

 

 

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

 

 

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

 

variance.png

 

Regards

 

Phil

 



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.