Calculating a value using data in two separate columns

[Anonymous]

I have two models - Index and Porfolio. I need to calculate the difference in weightings between the two. The models have a relationship based on the unique identifier (ISIN). There is also a relationship to a date table. Both models have ISIN numbers, Sector classification and weighting. The calculation is simply Portfolio Weight - Index Weight. I have tried using Lookupvalue to bring the Index Wt from the Index Model into the Portfolio model but I get the wrong answer. Can you suggest a solution - attached is a link to the test file https://1drv.ms/u/s!AiRs1BCuVWhM9DdsNuAEJUXfWZe7 

[Anonymous · Accepted solution]

@Anonymous,

In your PBIX file, I find that you create one-many relationship uising ISIN field between the Index table and Porfolio table. However, the Index table doesn't contain all the ISIN records of Porfolio table, you can review the following table visual. This is the reason why you don't get correct value of  Portfolio Weight - Index Weight.


But for those match ISIN records, you can get correct number for  Portfolio Weight - Index Weight.


Regrads,
Lydia

[Anonymous]

Hi Lydia, 

Thanks so much for your help. Makes perfect sense and apologies that I didn't spot the error in my data.