Data analysis displayed at Dashboard

Hi!

I am trying to make a good/clear visual data analysis on a dashboard in Excel. I have collect my incomes and expenses through the 2019 year, now I want to analyse it.

The situation:
I got a worksheet where the data is collected, from this data I have made a PivotTable, and this is where I got stucked. I am not very familiar with PivotTables and can't create a 'nice' PivotChart with the current PivotTable. In the PivotChart I want to see how much money comes in per month and goes out, then I want to see if this outcome per month is positive or negative. For example:

In the month January, I have spent -€650,- on the other hand the income was +€500,-. This makes the total of the month -€150,-.

In the end, this should be displayed on a Dashboard. Because if I have a better understanding of the possibilities with Excel, I probably want to compare it with other years (2020 for example).

The files I am working at are been attached in the appendix.

Like to hear if I am on the right track. Any help would be great!

Hi there,

A pivot table will summarise your amounts as you noted.
To compare Income against expanditure, you need to keep them in separate columns.
Keep both as poitive numbers to get an income vs Expense comparison per month.

If you keep the expenses negative, they will be showing as an opposite to the income.


I have added an additional calculated field to your pivot table, showing the net amount for the month (Income - Expenses).
You can plot it in the chart, or add as a secondary axis...

HI there,

Sorry I just did transfer the data to show you what is required, data in separate columns.

Yes you can use formulas for this. See attached updated file.
Basically, if the amount is greater than 0 then place the figure in Income, if the amount is less than 0 then place the negative amount into the expense column (making it positive).


Column Sum of Net Position in the Pivot Table is a calculated Pivot field.
Click inside the pivot table. On the Pivot Table Tools in the top Ribbon, Analize, Select Fields, Items & Sets. Then calculated fields. Here you can define calculations.
To see the formula I used, click on List Formulas. This will show you all calculations setup in the file.