Forum Discussion
Multi year budgets v actual
Hi all, new to power BI and need some help with the intial setup for a dashboard - Essentially this is a budget to expense report.
I have 2 fact tables:
1: Budget = Budget data is on an annual basis broken down by departments and sub departments
2: Transactions = The expense transactions are on a daily basis
At any given time i want to compare the actual expenses for a department or sub department to its annual budget - i.e in 2022 i want to compare the annual budget for Department A (700 in total) to the actual expenses from the daily expenses .. i would also like to further break this down and see the sub department budgets to their expenses.
This is my initial setp up:
I am having issues around comparing annual budgets to daily transaction amounts and then slicing by departments or sub departments.
Any advice on how to struture my fact tables and create the relationships will be much appreciated.
Thanks in advance
7 Replies
- danextian
Super User
Hi arumel ,
The annual budget can be split into several periods. The question now is what periods (days, months?) it is going to be split into? Also, provide a workable sample data ( not an image) that can be copy pasted into Excel or you can provide a link to an Excel file in the cloud.
- arumelFrequent Visitor
Hi, I only want to compare the annual budget to the expenses for that year .. i.e only need to compare the total 2022 budget to the total 2022 expense for a deprtment or sub department (dont need to brekdown to monthly budget details).
Below are the tables copied:
Deaprtment Sub Department Department Code Year Budget A A1 A1.1 2021 100 A A2 A2.2 2021 150 A A3 A3.3 2021 200 A A4 A4.4 2021 120 B B1 B1.1 2021 175 B B2 B1.2 2021 180 B B3 B1.3 2021 300 B B4 B1.4 2021 180 A A1 A1.1 2022 160 A A2 A2.2 2022 200 A A3 A3.3 2022 240 A A4 A4.4 2022 100 B B1 B1.1 2022 160 B B2 B1.2 2022 145 B B3 B1.3 2022 300 B B4 B1.4 2022 200 A A1 A1.1 2023 200 A A2 A2.2 2023 260 A A3 A3.3 2023 170 A A4 A4.4 2023 150 B B1 B1.1 2023 220 B B2 B1.2 2023 175 B B3 B1.3 2023 250 B B4 B1.4 2023 220 Trans Date Order No Vendor Currency Amount Department Code 1/1/2021 1011 1 USD 25 A2.2 1/2/2021 1011 1 USD 41 B1.1 5/8/2021 5055 2 USD 74 A2.2 9/9/2021 9099 3 USD 12 A3.3 4/4/2022 4044 2 EUR 36 A4.4 6/9/2022 6066 1 EUR 11 B1.1 1/12/2022 1211 2 EUR 36 B1.2 1/3/2023 1011 3 GBP 45 A1.1 1/4/2023 1011 4 GBP 93 A2.2 9/9/2023 9099 3 USD 44 A3.3 Thanks for your help
- danextian
Super User
Hi arumel ,
In your Actuals table, add a year column either using M or DAX. Then create a Year dimensions table and a Department dimension table. Relate both dimension tables to the fact tables. Use the dimension columns from the dimensions table in your visuals. Please see attached pbix for details.
- arumelFrequent Visitor
Hi all,
The above soluton works when the "Year" table is created as mentioned, however if i try and create a 'Dates' tables as a autocalendar and try and create a relatationship bertween the new 'dates' table 'Year' to the year columns in 'budget' and 'actual' tables, i get error meassages:
I would prefer a 'dates' table to to year on year comparisons ..
Any solutuions to these problems?
Thanks again
- AnonymousNot applicable
Hi arumel ,
Thanks for your feedback. That's only warning message due to both of the related tables(the table 'Dates' and 'Budget') has duplicated values. You can ignore it and click "OK" button to create the relationship for them directly.
Best Regards