Forum Discussion
Calculating Budget
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.
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
6 Replies
I would proberbly model it like this:
Should make it easy to maintain, filter and visualize.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.