Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Power BI use case - Developing a Personal Finance Calculator


By:   |   Read Comments   |   Related Tips: 1 | 2 | 3 | 4 | More > Power BI

Attend a SQL Server Conference for FREE >> click to learn more


Problem

Power BI can be used for many purposes and in this tip we will look at how to use Power BI to build a personal expenses score card.

Solution

Almost all banks have the facility to download bank statements in a CSV file format. In this tip, I will help you build a Personal Finance Tracker using Power BI. We will also create KPI’s and key indicators to understand how to track expenses and income.

Bank Statements

The image below represents the bank statement in a CSV format. This file has the following columns:

  • TransactionDate – The date in which the actual transaction happened
  • TransactionType – The type of transaction denotes the mode of transaction (Debit,Transfer,Standing Order, etc.)
  • TransactionDescription – Full details of the transaction
  • Debit Amount – Amount debited in case of expense
  • Credit Amount – Amount credited in case of income
Currency File - Description: Currency File

Import File into Power BI Desktop

Now let’s import the bank statement file into Power BI using Get Data > Text/CSV as shown below.

Import File - Description: Import File

Power BI has correctly identified the columns.

Importing Currency File - Description: Importing Currency File

Now let’s edit the query to modify the properties and name this “Transactions” as shown below.

Query Editor - Description: Query Editor

Now Close & Apply the query to save the changes.

Query Editor Close - Description: Query Editor Close

Creating Date Table with Attributes

Now let’s create a date table using calculated tables. This previous tip details the creation of a Date table with many attributes. As per the previous tip, the date table has been created with the DAX expression below.

Date Table - Description: Date Table

Now the date table can be extended with attributes such as Month, Quarter and Year.

Date table Attributes - Description: Date table Attributes

In addition, a relationship has been created between the transaction table and the date dimension table as shown below.

Date Table Relationship - Description: Date Table Relationship

Create Report

Now let’s create a simple report to list all the transactions.  A data table visualization has been chosen to represent the transactions.  As the relationship has been created between the Date table and the Transactions, now we can make use of the additional columns defined in the Date table for reporting.

The following columns have been chosen for this report.

  • Date (DimDate table)
  • CreditAmount (Transactions)
  • DebitAmount (Transactions)
  • TransactionType (Transactions)
  • TransactionDescription (Transactions)
Visualization for Report - Description: Visualization for Report

Now the actual report can be seen below, sorted by the most expensive debit.

transaction details

Creating KPI’s for Personal Expenses

Now let’s create a simple KPI to understand the overall expense and income. This can be easily achieved by adding the DebitAmount and CreditAmount columns to a multirow card. Power BI automatically sums all the values in the table and presents them in the multicard visualization.

Report Visualization - Description: Report Visualization

The actual report generated by Power BI can be seen below.

KPI Visualization - Description: KPI Visualization

The report above can be enhanced by adding another key metric “Expense ratio”. Expense ratio represents the spending, when compared with the income. The below DAX expression will be used to calculate the expense ratio.

Expense Ratio = Sum(Transactions[Debit Amount]) / SUM(Transactions[Credit Amount])

As this is a ratio, this measure has been formatted as “Percentage” in the modeling ribbon.

DAX for Expense Ratio - Description: DAX for Expense Ratio

In addition, the DebitAmount and CreditAmount columns have been formatted as currency columns with currency a symbol.

Now we can add the expense ratio measure to the multi row card and the KPI’s will appear.

KPI for Expense Ratio - Description: KPI for Expense Ratio

Detailed Analysis

Now we have developed high level KPI’s about the expenses. As the expense ratio is higher than expected, let’s dive deeper into the expenses. To take control of our expenses, we need to know which item cost the most and the sum of the remaining expenses. Also, to allow us to analyze income versus expenses, we need to calculate “Major expense % of total expense” and “Major expense % of total income”.

Calculating Major Expense:

Let’s create a measure “Major Expense”, with the below DAX expression. This will show us the most expensive debit.

Major Expense = Max(Transactions[Debit Amount])
Major Expense DAX - Description: Major Expense DAX

Other Expenses

As we have calculated the Major expense, now we can calculate the rest of the expenses. However, calculating the rest of the expenses is a little bit difficult because we need to exclude the Major expense in the calculation. This can be achieved by using the Filter table function.

The DAX expression below will help us calculate other expenses, excluding the Major expense.

Other Expenses = SumX(FILTER(Transactions,Transactions[Debit Amount] <> MAX(Transactions[Debit Amount])),Transactions[Debit Amount])

DAX evaluates the inner most expression first. In this case, the Filter table function will be evaluated first. The Filter table function accepts two parameters, name of the table and the expression.

  • Table Name – Transactions
  • Expression - Transactions[Debit Amount] <> MAX(Transactions[Debit Amount]). This expression will return all the records excluding the major expense

The Filter table function returns a table which consists of expenses excluding the Major expense. The outer SumX function summarizes all the expenses and returns the results as a measure.

Measures for Expenses & Income

Before creating other metrics, we need to create measures for income and expenses. These can be created using the DAX expression below.

The Income measure can be created using the expression below:

Income = Sum(Transactions[Credit Amount])

The Expense measure can be created using the expression below:

Expenses = Sum(Transactions[Debit Amount])

Calculating Ratios

Now with the help of the measures above, we can create measures for ratios.

The “Major Expense % of Total Expense” measure can be created by using the expression below:

Major Expense % of Total Expense = [Major Expense] / [Expenses]

The “Major Expense % of Total Income” measure can be created using the expression below:

Major Expense % of Total Income = [Major Expense] / [Income]

Report Visualization

All expense measures can be added to a multi row card and the report would look like below.

Report Dashboard - Description: Report Dashboard

Limitations

In the above example the Major expense and the sum of the Other expenses should equal the total expenses. However, if you have more than one transaction with the same highest amount, these duplicate values will be excluded because of the simple formula that was used.  In a future tip, I will help you overcome this problem.

Summary

The KPI’s above are a good way to start analyzing your personal finances. This Power BI model can be extended further to analyze the expenses on daily, weekly and monthly basis. In addition, the transactions can be logically grouped as a category and the expenses can be analyzed as well.

Next Steps
  • Stay tuned to read the next tip to analyze personal finance with daily expense and category analysis.
  • Read more about the Summarize function.
  • Read other Power BI Tips here.


Last Update:


signup button

next tip button



About the author





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools