Forum Discussion
Helper VRetrieve Unique Values-- Budgets vs. Actuals
I am hoping to retrieve unique values from my vendors on the expense types. I have two tables: Budgeted and Actuals. Only Actuals has Vendor Names and Unique Posting Dates, while the Budget Table has only one Date (11/13/2018), and both have expense types. I want to be able to see the budgeted amount for each Vendor (which is inside expense type) to get an accurate remaining total. I feel like I need to make a direct connection between my budgets and actuals table?
Hi bw70316,
Is there a Vendor name field in Budget table? If yes, you could establish a relationship between Actual and Budget table based on Vendor name. If not, what is the mapping relationship between two tables? How to determine each budget record in Budget table is matched with which vendor in Actual table?
Please simplify the sample tables for better understanding and illustrate your desired result with examples.
Regards,
Yuliana Gu
3 Replies
v-yulgu-msftMicrosoft Employee
Hi bw70316,
Is there a Vendor name field in Budget table? If yes, you could establish a relationship between Actual and Budget table based on Vendor name. If not, what is the mapping relationship between two tables? How to determine each budget record in Budget table is matched with which vendor in Actual table?
Please simplify the sample tables for better understanding and illustrate your desired result with examples.
Regards,
Yuliana Gu
bw70316Your summation was pretty much correct. This is data that I had just been given and had no personal access to aside from creating the relationships. Since the vendors are not available on the budget, they can't be tracked on a budgetary basis, just an actuals one. Thank you for your reply. I had to have a colleague confirm this suspicion.
Hi, I have a similar query, I have three tables
Table 1 Transaction Table :
Country Department Date of Request Spends
Below this I created a Calculated Measure called = Total Spends
Table 2 Date :
Date Year Month Mnt Number Day
Table 3 Budget:
Country Department Year Budget
Below this I created a Measure called Total Budget {Syntax used is = sum[budget])
Table 1 and 2 are connected via relationship (Table 1) Date of Request - Year (Table 2)
Problem -
When I wish to connect Table 2 and Table 3, it asks for Many to Many Relationship Year (Table 2) and Year (Table 3)
Now I am trying to create a final Table from these relationship -
As you can see above the total spends is categorised by Department but it is not the same for Budgets.
Can you please help. Thanks in advance!
