Calculating Budget

[Anonymous]

Good Morning Everyone,

I am trying to create a budget report that includes a comparison between money spent and the budget. Basically in the example im showing there are 5 different budget groups and there is a budget each year that is calculated based off of the total budget. ( Each Budget group gets a set percentage, that percentage is static and it never changes)

What is the best way to model this to visualize the data best. The following is just an example. The graph displays actual money spent and that is what I want to incorporate the budget into.

[Anonymous]

Ah I forgot to clarify, there are two large accounts, and 5 budget groups within each account. The data is from 2016-2019.

[tex628]

I would proberbly model it like this:


Should make it easy to maintain, filter and visualize. 

[Anonymous]

I see how that could work. So I am an intern at this company and they are looking for a system that automatically updates themselves. All they have to do is update it by inputting the annual budget, and all other calculations are made within Power Bi. So when I leave these reports can live on.

In addition to that, I am still wondering which visuals I can use to display that data. I hope I'm not asking for too much of a handout but i've been working on this for a while.

[Anonymous]

I guess in looser terms I want to have the table created within power bi that makes these calculations. I am just not sure how to do it.

[tex628 · Accepted solution]

I made a small recreation of your budgetfile:

Then i used this code in power query:

Query 1 - Budget:

let
    Source = Excel.Workbook(File.Contents("YOUR FILE HERE"), null, true),
    Budget_Sheet = Source{[Item="Budget",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Budget_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Year", Int64.Type}, {"Total Travel", Int64.Type}, {"Total OBE", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Type", each List.Numbers(1,5)),
    #"Expanded Type" = Table.ExpandListColumn(#"Added Custom", "Type"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Type",{{"Type", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1","1","A",Replacer.ReplaceText,{"Type"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","2","B",Replacer.ReplaceText,{"Type"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","3","C",Replacer.ReplaceText,{"Type"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","4","D",Replacer.ReplaceText,{"Type"}),
    #"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","5","E",Replacer.ReplaceText,{"Type"}),
    #"Merged Queries" = Table.NestedJoin(#"Replaced Value4",{"Type"},Percentages,{"Budget"},"Percentages",JoinKind.LeftOuter),
    #"Expanded Percentages" = Table.ExpandTableColumn(#"Merged Queries", "Percentages", {"%"}, {"Percentages.%"}),
    #"Divided Column" = Table.TransformColumns(#"Expanded Percentages", {{"Percentages.%", each _ / 100, type number}}),
    #"Added Custom1" = Table.AddColumn(#"Divided Column", "Travel budget", each [Total Travel] * [#"Percentages.%"]),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "OBE Budget", each [Total OBE] * [#"Percentages.%"])
in
    #"Added Custom2"

Query 2 - Percentages:

let
    Source = Excel.Workbook(File.Contents("YOUR FILE HERE"), null, true),
    Percentages_Sheet = Source{[Item="Percentages",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Percentages_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Budget", type text}, {"%", Int64.Type}})
in
    #"Changed Type"

This should result in a budget model that will update itself automatically when hey enter the amount for a new year:


In terms of how you should visualize it, you should explore on your own and talk with whoever ordered this. I made some quick examples based on what you posted:



I hope this helps! 

Br,
Johannes

[ChrisMendoza]

@Anonymous -

I found this article helpful https://www.daxpatterns.com/budget-patterns/. I see some similarities in the granularity  and allocation methods that you might be able to implement.

The presentation at https://www.sqlbi.com/tv/budgeting-with-power-bi-pass-austria/ was also helpful to me in solving more complex problems in regards to budgeting.