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))
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.
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))
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.