Combing Tables in PowerBI

[scorrera]

I'm sorry if I don't articulate this well.

Overall I'm looking to create a visual that can compare budgeted amounts versus what was actually spent in a center-account on a monthly basis.

Currently I have the actual spend numbers that come out monthly and having the following columns: center, account, debit and credits. I currenlty have those going into text format to a file that PowerBI looks at and pulls in. I have query to merge center and account columns to get a center-account column and then subtract debits from credits to show spend. This part is working fine and shows me what I want. Below is an example. I have by month the total spend for a center-Account.

I now need to bring in the budgeted amount. That data comes in the form of center, account, and month columns(sept, oct, nov, dec). In other words each month is its own column. I ideally I'd like to display on the above graphic another column called budgeted amount with what was the budgeted amount for each month and center-account.

I've tried numerous ways but I'm just not skilled enough to make it work. If I bring in the budget data and create a relationship on center-account I get something like this. 

If I try to rework the budget data and make the column be center, account, budget amount, and have a seperate file for each month that I put into a folder I run into a many to many relationship situation. Since for every month I add to the folder it has the same center-account values as the previous month for both the actual spend data and the budget data. If I try to bring in the budget data as separte tables I get something like this.

Thanks in advance

[audreygerred · Accepted solution]

Hi! You're going to want to set your model up as a star schema so that you have fact and dimension tables. In this way you can have your actuals table and your budget table in as facts, and a date table that is the dimension. It would be joined to each fact table and that allows you to utilize the facts together in a visual through the join to the dimension (i.e. join facts with dimension, not fact to fact). You'll also need a dimension table for Center-Account which would also join to both facts. Then, in the visual you would pull in Center-Account from your dimension table, you month from a date table, and the actual and budget would be measures based on the fact tables. Here are two blogs posts I have about star schema and a great table I love: http://powerbiwithme.com/2023/07/30/the-star-schema-edition/ and  http://powerbiwithme.com/2023/08/01/the-custom-date-table-edition/ 

[Daniel29195 · Accepted solution]

@scorrera 

for your budgeted table , go to power query and unpivot the table 

this way you will have the month on the rows and per each row th budget ,

now you can link your dimdate table ( assuming you have one.  if not, i recommened that you create one ) , 

to both tables. 

and then you just need to drag and drop the columns from the tables to the visual : 

dates from dimdate, 

actual spend from the first table , and budget amount from the budget_amount_table .

@scorrera  let me know if this works for you .

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

[audreygerred · Accepted solution]

Hi! You're going to want to set your model up as a star schema so that you have fact and dimension tables. In this way you can have your actuals table and your budget table in as facts, and a date table that is the dimension. It would be joined to each fact table and that allows you to utilize the facts together in a visual through the join to the dimension (i.e. join facts with dimension, not fact to fact). You'll also need a dimension table for Center-Account which would also join to both facts. Then, in the visual you would pull in Center-Account from your dimension table, you month from a date table, and the actual and budget would be measures based on the fact tables. Here are two blogs posts I have about star schema and a great table I love: http://powerbiwithme.com/2023/07/30/the-star-schema-edition/ and  http://powerbiwithme.com/2023/08/01/the-custom-date-table-edition/ 

[scorrera]

Thank you for your repsonse. I think it has moved me forward but I'm still not there. This is what I have currently:

And it gives me these results:

FYI I did unpivot the data in the budget table so it looks like this:

[scorrera]

Actually I think I was able to do it by setting the attribute column from unpivoting the months to a date format.

[Anonymous]

Hi @scorrera ,

Whether your problem has been resolved? If yes, could you please mark the helpful post as Answered? It will help the others in the community find the solution easily if they face the same problem as yours. Thank you.

Otherwise, could you please provide some sample data(exclude sensitive data) and explain your current situation? It would be helpful to get the solution. Thank you.

Best Regards