Power BI Financial Reporting & Financial Analysis

Image: monticellllo/Adobe Stock

Introduction:

Power BI is a powerful business intelligence (BI) tool that Microsoft developed. It is indeed one of the most requested and popular reporting tools in the job market. Power BI has a variety of features that can be used to support multiple types of analytics such as Diagnostic Analysis, Predictive Analysis and Statistical Analysis. With Power BI, organizations can connect to hundred of data sources, build complex relational model and create visually appealing visualizations to deliver important messages.

As a Business Analyst Intern, I have had a chance to work extensively with Power BI and use it to lead different reporting analytics projects. Fascinated by the impact that this BI platform could have on an organization, I want to take this opportunity to demonstrate the features that Power BI can offer using my most recent project of designing a high-quality, end-to-end financial report from the ground up to the client. Along the way, I would include some of my personal tips and advice on how to:

>>> Optimize the ETL (Extraction, Transform, and Load) process to get the best quality data.

>>> Build complex but efficient relational data model to ensure correct relationships and accurate mathematical calculations.

>>> Design a stunning, interactive dashboards that can easily capture audience attention.

>>> Share the report dashboard with others to promote best collaboration, even with those who are not familiar with Power BI interface.

Background Information and Objectives:

Situation: Our client is one of the global leaders in enterprise level-loyalty and engagement platform solutions and experience design. For the last couple of years, their finance team has relied heavily on Excel to conduct sales reports and uploaded them onto Dynamic 365. However, those reports are just simple tables and matrixes that include sales numbers broken down either into months or by divisions. They do not offer a holistic view of what the raw data is trying to convey and can become difficult for viewers to identify patterns and extract insights. Furthermore, it takes a long manual process to collect, transform and input the data into the spreadsheet. Below is one example:

Objective & Deliverables: taking on a role of an analyst, I have to determine a way to deliver a viable intelligence solution, armed with Power BI, to the client and enable them to eliminate the current hectic process. After having multiple meetings and discussions, the client and I agree to break down the project into 4 phases, including:

1. Using the current dataset to build a report that acts as a standard template and submit for the approval from the executives. The report itself has to showcase the capability to simplify complex data information through effective visuals.
2. Figuring out a method of establishing a live connection for new data to be updated in the report without having too much labor work involved.
3. Making necessary changes to the report (layout, data pieces, etc.) based on feedback from executives until it can be implemented organization-wide.
4. Identifying the best way to scale the report to others outside of finance department and giving everyone a walkthrough of how to utilize Power BI Desktop and Power BI Service for successful collaboration.

In-Depth Process and Insights Summary:

The current sales datasets are packed with information that is worth to be explored. Since they contain both historical and real-time data, I decide to build two different dashboard reports for the client, which are “Year-over-Year Revenue” and “Year-over-Year Gross Profit”. The process is not as simple as it sounds because there are anomalies, duplicates, inconsistent formatting within those datasets. My very first step is to resolve this issue so the reports could be built upon factual and accurate data.

Data Cleaning —

By integrating different data transformation tools on the Query Editor (Text, Number and Date tools) along with fundamental DAX functions, I am able to turn “dirty” data into good-quality and easily-manageable data. During the process, I learn about the importance of understanding the data context and how queries work. These good practices, although require little bit extra effort and research, allow me to perform accurate operations like correct filtering, removing redundant columns, and appending/merging queries. Below here is the result:

The dataset after the cleaning process with the applied steps showing how to achieve such result

Tip: utilizing the Advance Editor or the Formula Bar to quickly review the DAX code that Power Query uses to generate each Applied Steps. In there, you can create your own shaping code or modify the existing code to arrive at the query quality you are looking for. Another thing to keep in mind is to keep yourself organized before loading the data into Power BI. For instance, try to establish a file/folder structure that makes sense right from the start, to avoid having to modify the data source settings if file names or locations change.

This is the code associated with each query steps I perform. I can easily modify the code and add more controls over my data presentations.

Data Modeling and Calculated Measures —

The next steps are to enable tables to communicate with each other and generate the right calculations to answer complex questions. In my case, I want to go deeper into the dataset by examining additional metrices such as what are the revenue/ gross profit generated by each customer, how they change over time, and what are some best performing segments in the company. To do this, I build a star schema relationship between the main table to other smaller tables including Budget Details, Date, Customer Dimension, and Segment. This allows a synchronized connection between different data entities, giving me the freedom to work with all the fields and calculate results accurately

The star schema model that I implement for the report

Speaking about measures, time-intelligence formulas work best for this type of analysis, which emphasizes on discovering trend based on time periods. I will present some of the formulas that I use and how they contribute to my finding.

1. DATESINPERIOD() Function: I use this DAX formula to calculate the running total of the company’s 10-days rolling revenue and gross profit (Exp: Running Total = CALCULATE([Revenue, DATESINPERIOD(Calendar[Date], MAX(Calendar[Date]), -10, DAY))
2. DATESMTD() Function: this particular expression allows me to see the accumulative total of generated revenue and gross profit from the beginning of the month until the end of year (Exp: Performance To — Date = CALCULATE([Revenue], DATESYTD(Calendar[Date]))
3. SAMEPERIODLASTYEAR() Function: I use this to compare the difference between the revenue and gross profit figure of the current year to the one of previous year. From there, I being able to calculate the gross profit margin for each month and for each customer (EXP: RevenueAmountPrevious = CALCULATE(SUM(Sales[Revenue]),SAMEPERIODLASTYEAR(Calendar[DueDate]))

Tip: the right Data Model is essential for correct DAX calculations. Therefore, you should spend enough time to review and establish the right relationships and cardinalities between tables. The good advice I get is to have one main table, often referred to as Data table, that can include repeating records and have separated multiple dimension tables (Date, Customer Information, Sales Location, etc.) that has unique value and serves a distinct purpose connecting to it through one-to-many cardinality.

The Matrix I create with applicable DAX Calculations that capture the overall financial picture of the company

Data Reporting with Appropriate Visuals—

After going through a rigid process of wrangling the data, this is where I bring the raw data to life. Some of the visuals I employ for these reports are KPI, Matrix, Bar Chart and Slicers. In addition, I pay close attention to the format, structure and details of each visual, which often are overlooked. On my terms, I want my visuals not only contain the factual data but also deliver those values in the most coherent and appealing manner. This would allow the audiences to throughout understand the information, to navigate the report with ease and finally be able to make data-driven decisions. Here are the end products:

Year-over-Year Gross Profit report by Entity, Segment and Customers

More details on Year-over-Year Profit

Year-over-Year Total Revenue with strong focus on customers

Tip: often, people fail to choose appropriate visualizations for their data. The visuals might look great but do not present information in a way that can be easily understood . Therefore, it is extremely important to consider the use of visuals that fit your analytical narrative and can deliver apparent message immediately to the audience. For example, I would consider using Pie Chart or Tree Map for comparative analysis. With these visuals, business would right away know how they perform in different segments or divisions and allocate resources accordingly.

Data Results and Further Improvement—

The above reports are equipped with great insights that our client now can use to analyze their company performance within the last 2 years and to come up with effective management decisions afterward. For example:

1. There is a big disparity in gross profit (GP) between December 2021 and December 2022. In particular, December 2022 GP is much lower than that of 2021 ($11,907,291 vs $6,888,995). This could be attributed to multiple factors such as the change in sales price, the drop in volume sold or the decrease in number of new customers acquired.
2. By total revenue, Augeo FI Loyalty is the number one customer for the last 2 years. Their revenue accounts for almost 79% of the total revenue of the company. Hence, more resources and more budget should be directed to this client to help maintaining their relationship and potentially selling them more services.

Also, to avoid having to manually input new data into spreadsheet like before, I set up a gateway connection from these reports to Dynamic 365 (where the datasets are resided) and implement the feature of incremental refresh so the visuals would be updated with new data every week. This is one the advantages of Power BI that my client is not aware. They are really impressed with the new procedure I introduce because it could help to cut down the labor work and improve the overall operational efficiency.

Tip: there are two concepts of data refreshing, which are full refresh and incremental refresh. Use full refresh if your current values are subjected to change at any point. It is recommended to do full refresh once a week. Use incremental refresh if you just want to bring in new rows of data. Incremental refresh could be done daily to help your report be updated with the most recent figures. Once again, both options can be scheduled to be automated.

Results Sharing —

Unlike Excel where collaboration could be a little bit of hassle, Power BI offers a robust cloud based service for sharing and collaboration called Power BI Service. With the right permission, people within the company can view the works that others create and use them for their own analytical purposes.

If I publish my reports above onto Power BI Service and I grant access to another department, they can extract the key visuals to create a customized dashboard that meet their need or even download and work with the ready-to-use datasets without having to repeat the ETL process. These intuitive features can help the enterprise to produce new insights at a much faster space and greater volume for enhanced decision making. In the final meeting, I summarize the main advantages of Power BI Service to our client and give them with a detailed document on how to properly manage this cloud based infrastructure to optimize collaboration and data sharing. They are totally convinced by the proposed solution and decide to try out the new process in their Finance department.

Project Outcome and Final Thought:

According to the client, the reports I build are exactly what they have been looking for. Now, they greatly contemplate of migrating away from Excel for sales reporting and using my solution as a new method. It might take some time until they can decide whether or not to implement my solution organization-wide, but nevertheless, the receptions that I have received have been really positive so far. Overall, this project has helped me to strengthen my knowledge on Power BI and understand the key components to design a high-end business intelligence solution that can improve data analytics capability and workflow efficiency for the organization. I hope you find this article helpful and maybe discover some new ideas or tips that you would consider to incorporate in your next finance project. Let me know your thought and thanks for reading through the article !!!