Bank Budget to track revenue

[mmldogs]

Hello
I am trying to analyze my bank data to check that I don't go over my allotted budget allowance of $5200 per month.
I have downloaded the data and placed into categories and have got totals for these categories by month.
I would like to show the monthly category as a percentage of my budget allowance of $5200. Doing basic % calc in power pivot is giving an error .
How do I get the $5200 figure into the table to do the calcs ?
Eventually I want the table to have monthly data added and charts created to show

• categories monthly as % of budget allowance
• Charts to show if monthly category totals are over or under the budget allowance.
• Charts for year to year budget progress

Haven't been able to find a solution. If there is a previous thread please let me know or if a solution can be provided that would be greatly appreciated

 

[AlanSidman]

I used Power Pivot and created a measure in DAX

=SUMX(Table1,Table1[Debit]/5200)

Then added that measure to values.

Data Range

	A​	B​	C​	D​	E​	F​	G​	H​	I​
3​	​	Date (Month)​	Date​	Values​	​	​	​	​	​
4​	​	Jan​	​	Feb​	​	Mar​	​	Apr​	​
5​	​	​	​	​	​	​	​	​	​
6​	Category​	Sum of Debit​	budget%​	Sum of Debit​	budget%​	Sum of Debit​	budget%​	Sum of Debit​	budget%​
7​	Bills & Payments​	1260.43​	24.24%​	781.73​	15.03%​	875.96​	16.85%​	1034.17​	19.89%​
8​	Entertainment & Recreation​	245.2​	4.72%​	342.67​	6.59%​	895.1​	17.21%​	738.18​	14.20%​
9​	Fees & Charges​	159.13​	3.06%​	100.46​	1.93%​	161.71​	3.11%​	115.28​	2.22%​
10​	Food & Beverage​	1368.09​	26.31%​	1377.66​	26.49%​	1770.83​	34.05%​	1165.11​	22.41%​
11​	Home & Property​	667.67​	12.84%​	262.89​	5.06%​	1393.83​	26.80%​	5002.7​	96.21%​
12​	Membership​	​	​	649.11​	12.48%​	533.11​	10.25%​	69.11​	1.33%​
13​	Memberships​	69.11​	1.33%​	​	​	​	​	​	​
14​	Retail & Personal​	1065.4​	20.49%​	598.48​	11.51%​	992.38​	19.08%​	1182.67​	22.74%​
15​	Transport & Travel​	328.38​	6.32%​	127.45​	2.45%​	252.29​	4.85%​	4895.3​	94.14%​
16​	Grand Total​	5163.41​	99.30%​	4240.45​	81.55%​	6875.21​	132.22%​	14202.52​	273.13%​

 

[AlanSidman]

Don't know what your issue is. I added a month of data and then clicked on Refresh All and the Pivot Table updated appropriately. Perhaps if you uploaded your file with new data it might help.

 

[mmldogs]

Hi
I have just deleted the previous reply . It was problem my end on "refresh "works really well. Thank you for your expertise and time