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

 

Analyzing Personal Finances using Power BI


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

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


Problem

In a previous tip we developed a Personal Finance Calculator in Power BI. In this tip, we will enhance what we did in the first part of the series to get a better understanding of how to use Power BI features.

Solution

It is recommended to read the previous tip here to develop the Personal Finance Calculator. In this tip, I will be showcasing how to extend the functionality of the application further.

We will cover how transactions can be grouped into transaction categories and how these categories can be used for further analysis. In addition, we will create more metrics to analyze the transactions. At last we will create a dashboard to represent daily expense analysis and expense analysis by category.

Creating a Simple KPI

We will be creating three KPIs named “Latest Expense Date”, “Oldest Expense Date” & “Total Number Of Transactions”.

A measure “Latest Expense Date” has been created with the DAX expression below

Latest Expense Date = Max(Transactions[Transaction Date])

A measure “Oldest Expense Date” has been created with the DAX expression below

Oldest Expense Date = Min(Transactions[Transaction Date])

A measure “Total Number Of Transactions” has been created with the DAX expression below

Total Number Of Transactions = COUNT(Transactions[Debit Amount])
Latest Expense Date - Description: Latest Expense Date
oldest Expense Date - Description: Oldest Expense Date
Total number of transactions - Description: Total number of transactions

These KPIs have been added to the dashboard and the image below represents this data.

KPIs - Description: KPIs

Daily Expense Analysis

Now the daily expenses can be analyzed using the column chart in report mode. As the image below shows, the transaction date can be added in the axis and the expenses can be added to the value field. Once added, the chart will look like the image below.

Daily Expense Analysis - Description: Daily Expense Analysis
Configuration - Description: Configuration

As the expense transactions are spread across many dates in a given month, the labels are shown only for a few. However, this can be resolved by changing the Type to “Categorical” in the X-Axis.

Report panel Configuration - Description: Report panel Configuration

Now the label details are available for every day in the chart.

Daily Expense Analysis - Description: Daily Expense Analysis

Expense Analysis by Category

To support expense analysis by category, I have created a lookup data in an Excel sheet. This sheet has only two columns named TransactionDescription and TransactionCategory.

Lookup Excel Sheet - Description: Lookup Excel Sheet

This Excel workbook can be imported into the Power BI model as shown below.

Import Excel sheet - Description: Import Excel sheet

The name of the query/table has been renamed as TransactionCategoryLookup. I have noticed that the column names available in the first row were not used by Power BI.

Edit Query - Description: Edit Query

This can be easily achieved by using the option “Use First Row As Headers” as per the image below. Now the columns have been defined correctly for the lookup query.

Import Query - Description: Import Query

Now close and apply to create the lookup table named “TransactionCategoryLookup”.

Lookup Table - Description: Lookup Table

Creating a relationship

Now let’s create a relationship between the Transactions table and the TransactionCategoryLookup table with the TransactionDescription column. This relationship will help us do a lookup in the Transactions table.

Relationship - Description: Relationship

A calculated column “TransactionCategory” can be added in the transactions table to lookup the value of TransactionCategory from the lookup table. This can be achieved by using the DAX expression below.

TransactionCategory = RELATED(TransactionCategoryLookup[TransactionCategory])
New calculated column - Description: New calculated column

As we have the TransactionCategory, now we can develop a report to analyze the expense by Transaction Category.

This can be achieved by adding a table to the report pane and selecting the columns “TransactionCategory” and “Expenses” from the transactions table. The image below represents the transactions table and the selected columns in the report pane.

Configuration for table - Description: Configuration for table

The report will be displayed with Transaction Category and expenses as shown below.

Category Analysis Report - Description: Category Analysis Report

A pie chart can be developed for a visual representation. The same two columns “Transaction Category” and "Expenses" in the transactions table can be used for this report as well.  The image below shows the Transaction Category column has been identified for the Details section and the Expenses column has been identified for the Value section.

Transaction Category Configuration - Description: Transaction Category Configuration

Now the pie chart has been generated for the expense analysis by category. The pie chart represents the percentage of overall expenses for a category. If you hover over a specific category, the actual expense amount for that category will be displayed in the tooltip.

Pie Chart for Analysis - Description: Pie Chart for Analysis

In addition, a specific category can be highlighted by selecting that category. The category “Food” has been highlighted in the image below.

Pie Chart with highlights - Description: Pie Chart  with highlights

Now click anywhere on the pie chart to go back and display all category expenses.

Displaying Blank Values

In the report it displays “Blank” as a category. This means for some transactions we don’t have the category in the lookup table.

The image below represents the “blank” category in the table and the pie chart.

Report with Blank category - Description: Report with Blank category

If you prefer not to display the blank category at all, then you can do so using the advanced filter. As we have many reports showing blank as category, the filter can be applied at the page level.

On the page level filters, select the Transaction Category column and select the advanced filtering options. On the drop down select the “is not blank” value. Once done the blank category will be removed and the report will look like below.

Report without blank - Description: Report without blank

Although this is a solution to remove the blank category, the risk is not displaying all of the transactions. Let’s say we have many transactions not being mapped, then we will be missing out on these details in the report. So it is always good to include the blank category in the report or try to map these transactions to categories.

So to improve the presentation, we can group all the unmapped transactions into “Others” category. This can be done by enhancing the DAX expression for the lookup column.

Now let’s add a new calculated column “TransactionCategoryNew” to the transactions table as shown below.

DAX Expression for New category - Description: DAX Expression for New category

The below DAX expression has been used to calculated the new column.

TransactionCategoryNew = 
var LkpValue= RELATED(TransactionCategoryLookup[TransactionCategory]) 
return

IF(Len(LkpValue)=0,"Others",LkpValue)

In the above DAX expression, the value of TransactionCategory has been looked up in the lookup table using the related function and the lookup value will be stored in a variable “LkpValue”. In the next step the variable will be validated for a blank value using the length function. If the value is blank then the string “Others” will be returned. From the above image, it is clearly visible that the new DAX expression has provided the value as “Others” for unmapped values.

Now let’s replace the new calculated column “TransctionCategoryNew” with the old column on the table and the pie chart.  From the below image it is confirmed that the “Others” category is visible and this will help us to understand the unmapped categories.

Report with Others category - Description: Report with Others category

Summary

In this tip, we have enhanced the Power BI model to analyze expenses based on Category. This model can be extended further to include budgeting analysis.

As individuals tend to have multiple bank accounts (personal, current, credit card, etc.) the format of the transactions also may be slightly different. In the next tip, I will help you extend the model to accommodate this functionality.

Next Steps


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