Create a Calculated Column from Different Tables

[CameronP]

I'm having a very difficult time bringing information from one table into another.

I have a Budget file with a data in it, such as branch, fund, budget, etc.

I also have salary files with data in it such as branch, salary, fund, etc.

How do I transfer the column salary from the salary file into the budget file to show the salary in the branch?
When I try and merge the tables for some reason it shows me the sum of the branch and not the individual transaction.
I have a branch library which connects the salary and budget file to it.

Thanks

[Phil_Seamark]

HI @CameronP

If you could mock up your tables using fake data, and share the tables here we can probably help you.

This is something that can be done a number of ways, but you'll get a better response if you can post some sample data.  Use Excel to help generate the sample if that helps.

Phil 🙂

[CameronP]

Thanks for the reply Phil.

How does this work?
https://imgur.com/v3aeMEg

Imagine the Budget File and Salary File as two seperate files, and the Branch Library as an excel file that is linked (relationship) with the Branch number in each file (Salary and Budget) so that I can filter the information by the Branch name.

I'm new to Power BI and am finding it difficult to be able to filter by both fund and branch name. So I need to be able to say:
I want to see what my budget and salary is for branch "Orange" and fund 200" (there is much more data than 5 lines though haha).
I've tried merging the data and I can't, and I don't know enough about DAX to know if there is a way around. I've also tried filtering the data down to the fund inside of the actual query so that it only shows the funds I want to see, but unfortunately it's very time consuming as a few of my files are 1GB+. Any suggestions to be able to filter the information by both fund and branch?

Thanks so much for your time 🙂

[v-chuncz-msft · Accepted solution]

@CameronP,

You may use LOOKUPVALUE Function to add a calculated column in DAX.