Monthly Balancing with Power BI

Part two of generating my household budget..

In part one of this post, I generated a single table in Excel which combined all of my monthly expenditures from across different data sources. This table is automatically updated when the flat file of the new month’s statement is added to the source folder.

Building off of this sheet and visualizing my data in Power BI is the fun part for me. I am a big believer in data visualization. I believe that it makes understanding data more accessible to wider audiences. It can make it easier to discover insights and, when done correctly, allows for more powerful story telling with data. I am also a big believer in Power BI. As I have mentioned on my site, I spend about 70% of my working day using Power Query in Excel and Power BI. In fact, Power BI was actually the tool that introduced me to the Power Query engine, which causes great shock and disappointment among Excel users. But better late than never..

So first step, similar to what I did in Excel, is to create my two parameters. The first parameter defines the name of my file, in my case the ever original Budget.xlsx. The second parameter defines the folder or folder path where the file resides. Again, I have a bad habit of changing my file names and the structure and/or names of my folders. Parameters can save a lot of frustration because you can change the definition of the parameter (for example, the file name) in only one place as everything else refers to the parameter itself.

Using these parameters, I connect to my Excel workbook.

= Excel.Workbook(File.Contents(Folder&”\”&File), null, true)

One step I generally take when connecting to one of my workbooks to visualize is to filter only tables as a source to connect to. I do this because I will typically structure my data in this format and it eliminates any possible confusion on my part.

= Table.SelectRows(Source, each ([Kind] = “Table”)),
= #”Filtered Rows”{[Item=”Budget”,Kind=”Table”]}[Data]

As I had done most of my data manipulation and cleaning in Excel already, there were not many additional steps I needed to do. One additional step I did apply was to extract the month number and month name from the transaction date column in my data. This is made really easy through the Power Query interface. In the query window I select my TranDate column. On the ribbon, I navigate to Add Column and From Date & Time. From the drop down in Date, I select Month and then both Month, which extracts the month number (1 through 12), and Month Name, which extracts the name of the month.

= Table.AddColumn(#”Filtered Rows”, “Month.1”, each Date.Month([TranDate]), type number),
= Table.AddColumn(#”Inserted Month”, “Month Name”, each Date.MonthName([TranDate]), type text)

I love this date logic functionality and use it extensively in Power BI. I will probably write a post at a later date outlining some instances where this has been an extremely helpful tool for me.

I closed and applied my query which loaded my report work space in Power BI desktop.

Now, visualizing and analyzing my data. My main goal of visualizing my budget was to be able to quickly identify trends and possibly outliers in my spending habits. With this in mind, a line chart to visualize trends over time was a must. I needed to know the number of transactions I had made as well as the total spend for the month. I thought it would also be useful to identify the biggest transaction of the month, particularly when this was broken down into my spending categories I had coded. Additionally, I wanted to see the vendors or locations where I was either making numerous transactions or spending a higher amount.

After identifying some of the outcomes I wanted from my visuals, I had to create a way to achieve this analysis. The best solution quickly came to mind.. DAX! Now, I am relatively new to creating DAX measures (in fact, I am currently working my way through Matt Allington’s Learn to Write DAX, which I highly recommend). I had previously always relied on the Power BI interface to drive my visuals. Most of the time, this is a great solution and would be fine. This is especially true now after the release of Quick Measures in Power BI, which has been a great learning tool for me as I increase my competencies in writing more complex measures. However, I am now a believer that writing your own measures allows for a greater flexibility in building visualizations and insights from your data. So, I created four DAX measures for my report:

  1. AmountSpent = SUM(Budget[Amount])
  2. TotalTxns = COUNTROWS(Budget)
  3. MaxAmount = MAX(Budget[Amount])
  4. DistinctVendors = DISTINCTCOUNT(Budget[Description])

These four measures generate the insights I outlined above. AmountSpent sums the amount from each of my transactions, giving me my total spend. TotalTxns counts all of the rows, and therefore transactions, in my budget table. MaxAmount returns the highest transaction amount, allowing me to identify my largest transaction for the month or within my different categories. DistinctVendors counts the number of distinct transaction descriptions from my budget table. This assumes that all vendor descriptions are distinct from one another and will remain unchanged for each vendor. This obviously will not be a perfect measure based on these assumptions, but it should be accurate enough to gain some interesting insights.

I will discuss three of my visuals below, although my full report consists of more. Normally I would publish and embed my dashboard in this post, but I did not want to release all of my spending information. These visuals are based on a subset of my transactions.

My first visual is a Line chart. Line charts are great for visualizing trends over time. In this case, my monthly spend by a few of my categories. To build this visual, I selected the Line chart visual in Power BI. I selected TranDate as the axis, Category as the legend, and my AmountSpent measure as the values. I did a little bit of formatting to the visual by increasing the font sizes and changing the font colors to black. I also prefer having both my title and legend at the top and in the center of the page.

The result is clean and several insights are quickly apparent:

  • my strata fee, internet, and hydro payments have remained consistent month over month
  • my spend on gas doubled in March
  • I only started paying for internet in March and for gas and groceries in February?

Some of these insights were are a result of data issues, which was me filtering out some of the transactions. These anomalies were not so clear when looking at a spreadsheet. By visualizing, it made them obvious and quickly questions were asked, which is exactly what is so great about data visualization!

My second visual is a Treemap. Treemaps generate the “wow factor” and can be powerful when used correctly. I tend to stick to using them for simple hierarchical data. In this instance, the count of transactions on my credit and debit card as the two branches and the spend categories as the leaves. To build, I selected the Treemap visualization with CardType as the group, Category as the details, and my TotalTxns measure as the values. I pretty much maintained the default formatting as it looked clean and easy to read.

My initial insights:

  • there are 2-3x as many credit card as debit card transactions
  • the “OTHER” category has the highest number of transactions for both card types
  • groceries, gas, and internet transactions were all completed on credit card whereas strata and hydro were all completed on debit card

Again, for this type of hierarchical data, a Treemap is a great choice. These insights were easy to pick out and lead to questions that may have been missed without the visual.

My final visualization is a Stacked bar chart. This is probably the simplest visualization and the one I use the most. I find it is the best option to visualize a comparison of a metric across categories. Here, I use it to compare the count of transactions across my spend categories. I selected the Stacked bar chart visual and selected Category as the axis and my TotalTxns measure as my value. A formatting change I almost always make with bar charts is removing the x-axis and create data labels. This declutters the visual and increases the readability. In my experience, consumers of this visual will always want to know the exact number for each bar. This is understandable as they want to ask questions of the data and compare with their own numbers to validate. All of which I think is great!

My initial insights:

  • the “OTHER” category once again dominates the number of transactions, which is consistent with my Treemap visual
  • strata, hydro, internet, and gas transactions all seem to occur about once a month
  • grocery transactions seem to occur weekly

These insights all pass the smell test based on the knowledge of my behavior. I will also have to further refine my “OTHER” category..

So, there you have it. Leveraging Power Query in both Excel and Power BI to consolidate, customize, and visualize my monthly transactional balancing. As I mentioned, I plan to write a post on what I have learned so far with DAX, as well as a deeper dive into data visualizations. I’m excited for my next post which I think will be on importing and manipulating data in R! As always, if you have a better or different way to achieve what I have outlined, please let me know.

Leave a Reply

Your email address will not be published. Required fields are marked *