Calculating a new column to bring in budget amounts accounting for three fields

timshel

New Member
Joined
Apr 8, 2018
Messages
5
I previously posed here with similar question, but wanted to be able to give a more indepth understanding of what my goal was.

I have two tables from my erp system that is connected to power bi. One is actuals and the other is the budget. What i have been trying to do is to create a side by side comparison in a matrix or something as a report and show the variance between the two.


Actuals Screenshot https://imgur.com/a/aPegZzf ((small sample of them))
iokN1HZ



Budget Screenshot https://imgur.com/a/iokN1HZ ((all budgets))


The budgets are broken down by account, department (which all will be the same here), location (all the same for this example, but more will add more later on), and project name. Then i have the actuals table that is similarly broken down, with a few exceptions, like no account title but an account number.


So my overall goal would be able to set up a new column in the actuals table, and then bring the budget amount for the actuals that match with the budgets. So in the new column it would have the budget amount from the budget table that matched with the accountno , Locationname , and projectname. Ive read that the CALCULATE function is what i would use, with filters and such, but with my lack of knowledge of this software, I havent been able to figure that out.


I tried to create a relationship between the two, but that didnt seem to work because it would be many to many. I tried to include lookup tables, but that didnt work but that may have just been my error.


Any help with this would be greatly appreciated, I am very excited to learn more about this software.
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top