Hi Chaps,
There are a few old posts around stuff like this but nothing that i can find that seems to get to the crux of what the problem i am having...
Bottom line - I want to be able to create a chart showing the balance in my bank account(s) over time
Project Outline:
- I have bank data imported into my Excel sheet through Power Query that can be updated every month by adding in a new account csv and it automatically refreshing the data in my sheet... works a treat - so i have all my account transactions from all my bank accounts from a number of banks i have my accounts in, put into a lovely data table... that includes the balance after each transaction from any of the accounts...
- I want to create a pivotchart from this data such that i can select any of my accounts and it then plot the balance of that account over time in a pivot chart
I cant seem to get the right format of the pivot table in place in the right way to just simply plot balance against date but it keeps summing the balances for each day. ... I have attached a screenshot of the type of simple format i think i need in Excel (and summaried below)
"Filter" for Account Name at the top, then a list of dates for the "rows" and for each date a balance for that day that goes in the "Values" field...
____________________
Account Name | drop down of account
__________________________________________
DAte | Balance
-
-
-
-
-
nothing complicated about that but i might be missing a REALLY simple trick here to just get the data in the right table output...i think i just need a way for it to display the Balance as the number as it appears rather than the "sum" of the balances that it displays by default. there is nothing in the Field Settings which allows me to just display the values
There are a number of posts I have found on topics like this that require an original statement of balance to start and then to plot the on-going balance by calculating it through transactions made but i'd like to see if i can get it all run directly from my imported account data on its own if i can so i have a minimum of additional stuff going on...as i have the information all there in front of me with the imported data anyway.
Thanks in advance...
Ian
Bookmarks