Forum Discussion

arumel's avatar
arumel
Frequent Visitor
2 years ago

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

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

    • arumel's avatar
      arumel
      Frequent 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:

       

      DeaprtmentSub DepartmentDepartment CodeYearBudget
      AA1A1.12021100
      AA2A2.22021150
      AA3A3.32021200
      AA4A4.42021120
      BB1B1.12021175
      BB2B1.22021180
      BB3B1.32021300
      BB4B1.42021180
      AA1A1.12022160
      AA2A2.22022200
      AA3A3.32022240
      AA4A4.42022100
      BB1B1.12022160
      BB2B1.22022145
      BB3B1.32022300
      BB4B1.42022200
      AA1A1.12023200
      AA2A2.22023260
      AA3A3.32023170
      AA4A4.42023150
      BB1B1.12023220
      BB2B1.22023175
      BB3B1.32023250
      BB4B1.42023220

       

      Trans DateOrder NoVendorCurrencyAmountDepartment Code
      1/1/202110111USD25A2.2
      1/2/202110111USD41B1.1
      5/8/202150552USD74A2.2
      9/9/202190993USD12A3.3
      4/4/202240442EUR36A4.4
      6/9/202260661EUR11B1.1
      1/12/202212112EUR36B1.2
      1/3/202310113GBP45A1.1
      1/4/202310114GBP93A2.2
      9/9/202390993USD44A3.3

       

      Thanks for your help

      • danextian's avatar
        danextian
        Icon for Super User rankSuper 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.

         

  • arumel's avatar
    arumel
    Frequent 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

    • Anonymous's avatar
      Anonymous
      Not 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