Financial Modeling in Power BI (Part 3)

/ Data Analysis, Data Engineering / By

Table of Contents

Overview

If you haven’t done so already, please make sure to review part one and two of this series:

Effectively, we are building financial statements and reports based on raw transactional general ledger (GL) data. In this article, we will be procuring the following financial statements and metrics:

  • Financial Statements: Profit and Loss Statement, Balance Sheet, Cashflow Statement
  • Financial Metrics: Profitability Ratios (Investopedia), Liquidity Ratios (Investopedia), Efficiency Ratios (Investopedia), and Return on Investment (ROI) Ratios. All metric definitions can be found in the section: Ratio Definitions

Indifferent of the organization or data structure, building financial reports and gleaning insights should be accessible to anyone with raw data and a will to automate and improve their processes.

Data Modeling

If you haven’t done so already, please reference the section Financial Modeling with Business Intelligence in part one of this series to see the optimal data structure and explore an interactive model | report. Effectively, we are pursuing a star schema with an outrigger designed for financial statements.

In this implementation, we are expanding on these concepts slightly by introducing a second fact table for budgets (more information about multiple fact tables: Handling Multiple fact tables in Power BI, Guy in a Cube). The model still follows star schema best practices and functions in the same way! You can review the Entity Relationship (ER) Diagram below:

An interesting question was posed by a client when presenting this information. Effectively, they asked when to apply a single consolidated fact table versus multiple fact tables. The answer lies in correlation of metrics. If a single fact table can express the same information (grain) and substantially reduce the number of rows, then implement a ‘consolidated fact table’ (Consolidated Fact Table, Kimball). However, if the grain is different, and there isn’t correlation between the tables (i.e., the result looks like a UNION of the tables), it’s probably best to keep them separate.

Lastly, it’s important to note the many-to-many relationship between the Calendar and Budgets table. The Budgets table is currently at the year granularity. Please note that if budgets are at a different grain (e.g., quarterly, monthly, etc.), then the relationship could change and the budget measure could potentially need adjustments.

ETL with Power Query

The Power Query ETL has remained largely unchanged since the initial model. A quick recap and update:

  • General Ledger and Budget data is sourced from here: Randomized Retail GL Generator, Analytical Ants
  • Calendar is procured from the first available General Ledger date. A new DAX calculated column indexes the month.
  • Chart of Accounts has a new ‘Debit Increase Indicator’. The Debit Increase Indicator codifies ‘which accounts increase with debits’ logic.
  • Financial Statements has a new Detail Sorted column which allows for dynamic sorting of duplicate values. More information can be found in the Bonus Power BI Features section.

Creating Financial Metrics with DAX

We will review metrics in the following order: financial statement metrics, financial ratio metrics, and other metrics.

Financial Statement Metrics

There are two main metrics that drive the financial statements: transactional and cumulative actuals. Profit and Loss and Cashflow statements (and respective accounts!) utilize transactional actuals; Balance Sheet statements and accounts use cumulative actuals.

Transactional Actuals

Transactional actuals simply aggregate based on the filter context (e.g., all transactions between a date range). These transactional actuals are used in the Profit and Loss statement as well as the Cashflow statement where you need to know the account changes over a period (i.e., increases/decreases per period). The logic is extremely simple:

Transactional Actuals = 
VAR __Logic = SELECTEDVALUE( 'Chart of Accounts'[Debit Increase Indicator] ) = 1
VAR __Debit = SUMX ( 'General Ledger' , 'General Ledger'[DR] - 'General Ledger'[CR] )
VAR __Credit = SUMX ( 'General Ledger' , 'General Ledger'[CR] - 'General Ledger'[DR] ) 
RETURN IF ( __Logic = TRUE , __Debit , __Credit )

Effectively, if the account increases with debits, add debits minus credits. Else, add credits minus debits. Going forward, we will simply call this metric, “Actuals”.

For the ‘DAX-quants’ out there, the remainder of this section is dedicated to you.

Intuitively, this equation makes sense, but how does it aggregate correctly? With a granular view, we can see that each account calculates correctly. But the question remains, how does the measure aggregate two ‘positive’ accounts within a category (e.g., Gross Margin, shown below)?

The answer lies at the intersection of DAX and finance. In the ‘__Logic’ variable, if there is more than one ‘Debit Increase Indicator’ value, a blank value returns. The metric is then evaluated as a credit, and because Revenue accounts increase with credits, the output for Gross Margin is a positive integer. 🤯

I built the script and still stumbled when I was explaining how the metric worked the first time 😂.

Cumulative Actuals

Cumulative actuals are exactly what they infer: cumulative values of all prior transactions. This is valuable for the Balance Sheet and respective accounts where the true balance is a cumulative sum of all prior transactions (until a certain point). The code looks like this:

Cumulative Actuals = 
VAR __LastAvailableDate = CALCULATE ( MAX ( 'General Ledger'[Date] ), All ( 'General Ledger'[Date] ) )
RETURN 
    CALCULATE( 
        [Actuals]
        , FILTER(
            ALL ( 'Calendar'[Date] ) 
            , 'Calendar'[Date] <= __LastAvailableDate
        )
    )

Effectively, find the last available with data, and sum everything up-until that point. Note how the cumulative actuals utilizes the transactional actuals from earlier.

Financial Ratio Metrics

Between the model and prior two metrics, we have everything we need to procure our financial ratios. There are two main components to consider:

  • Which ‘Actuals’ to use: if an account utilizes a Profit and Loss account (i.e., Revenue, Expense) utilize the transactional actuals. Else, utilize cumulative actuals.
  • Accounts to constrain: Because we have modeled aggregations|groupings in the ‘Financial Statements’ table, we will treat calculations a bit like SQL and will apply ‘constraints’ to that table. You will see more below.

We will demo two ratios as examples: Gross Profit Ratio and the Current Ratio

Gross Profit Ratio

Standard equation: Gross Profit / Revenue

DAX equation:

VAR __Revenue = 
    CALCULATE(
        [Actuals]
        , TREATAS( {"Profit and Loss"} , 'Financial Statements'[Financial Statement] )
        , TREATAS( {"Sales Revenue"} , 'Financial Statements'[Category] )
    ) 
VAR __GM = 
    CALCULATE(
        [Actuals]
        , TREATAS( {"Profit and Loss"} , 'Financial Statements'[Financial Statement] )
        , TREATAS( {"Gross Margin"} , 'Financial Statements'[Category] )
    )	
RETURN DIVIDE( __GM , __Revenue )

Notes: Notice how (1) we utilize ‘Actuals’ in the metric as these are ‘transactional’ accounts. (2) How we are constraining the appropriate accounts on the Financial Statements table. For example, Revenue constrains the Financial Statement column to ‘Profit and Loss’ and the Category column to ‘Revenue’.

Current Ratio

Standard equation: Current Assets / Current Liabilities

DAX equation:

Current Ratio = 
VAR __CA = 
    CALCULATE(
        [Cumulative Actuals]
        , TREATAS( {"Balance Sheet"} , 'Financial Statements'[Financial Statement] )
        , TREATAS( {"Assets"} , 'Financial Statements'[Category] )
        , TREATAS( {"Current Assets"} , 'Financial Statements'[Subcategory] )
    ) 
VAR __CL = 
    CALCULATE(
        [Cumulative Actuals]
        , TREATAS( {"Balance Sheet"} , 'Financial Statements'[Financial Statement] )
        , TREATAS( {"Liabilities and Owners Equity"} , 'Financial Statements'[Category] )
        , TREATAS( {"Liabilities"} , 'Financial Statements'[Subcategory] )
    )	
RETURN DIVIDE( __CA , __CL )

Notes: (1) We utilize the [Cumulative Actuals] metric as these are cumulative accounts. (2) not only constrain the Category to ‘Assets’, but also the subcategory to ‘Current Assets’. This allows us to target and only propagate desired accounts (note, our model only has current liabilities, so all liabilities are selected; we could change this in the future).

Other Metrics

Budgets

Budgets aren’t typically codified as ‘debits and credits’, but rather as a single ‘amount’. In this example, budgets are loaded at the account level and based on the final desired outcome. Therefore, we need only one metric to calculate the value based on the account.

The DAX expression is shown below:

Budgets = 
VAR __Debit = CALCULATE( SUM ( Budgets[Budget Amount] ) , TREATAS( {1} , 'Chart of Accounts'[Debit Increase Indicator] ) )
VAR __Credit = CALCULATE( SUM ( Budgets[Budget Amount] ) , TREATAS( {0} , 'Chart of Accounts'[Debit Increase Indicator] ) ) 
RETURN 
    IF (  
        HASONEVALUE( 'Chart of Accounts'[Debit Increase Indicator] ) 		
        , __Credit + __Debit
        , __Credit - __Debit 
    )

Effectively, if there is a single value for ‘Chart of Accounts’ [Debit Increase Indicator], then simply add everything up. Because all the accounts go in a similar way, adding the amounts as debits and credits only yields the correct positive amount. However, if there is more than one ‘Debit Indicator’ then subtract credits minus debits (reference the transactional actuals section for more information on how and why this works).

Variances

Variances are super straight forward— either take the delta of Actuals and Budgets or the percent change from Actuals and Budgets. There will be two variance types, one for transactional accounts and another for cumulative.

Ratio Definitions

Below are standard ratio equations as listed in Accounting & Financial Management for Residential Construction, Shinn:

  • Profitability Ratios
    • Cost of Sales Ratio = COGS / Revenue
    • Gross Profit Ratio = GP/ Revenue
    • Operating Expense Ratio = OE / Revenue
    • Net Profit Ratio = NP / Revenue
  • Liquidity Ratios
    • Current Ratio = Current Assets / Current Liabilities
    • Working Capital = Current Assets – Current Liabilities
    • Acid Test Ratio = Quick Assets / Current Liabilities
  • Efficiency Ratios
    • Asset Turnover Ratio = Revenue / Assets
    • Inventory Turnover Ratio = Revenue / Inventory
    • OE to Total Assets = OE / Assets
    • Ratio Liabilities to OE = Liabilities / OE
  • ROI ratios
    • Return on Assets Ratio = NP / Assets
    • Return on OE Ratio = Net profit / OE
    • ROI = (Profits / Revenue) * (Revenue / Assets) x (Assets / Equity)

Creating Financial Reports with Power BI

With the right model and metrics, report generation is now a breeze! However, one more step is needed. Each report dedicated to a Financial Statement needs a page level filter on that page. That’s it! Now dragging and dropping every metric and attribute will automatically be focused based on your desired financial statement.

Let’s work through building the Balance Sheet:

To build the balance sheet, bring over the following fields: ‘Financial Statements’[Category], ‘Financial Statements’[Subcategory], and ‘Financial Statements’[Detail Sorted]. Now, bring over your desired metrics: [Cumulative Actuals] , [Budgets], and [Cumulative Actuals Variance %]. Voilà! You have a Balance sheet that lists accounts and orders then appropriately.

Adding Ratios is even easier. Simply bring over your desired metrics to display KPI cards. You can use the new Card visual to add context such as budgets and conditional formatting.

Lastly, adding trendlines and visuals is also extremely easy. Simply bring over your desired metrics (e.g., [Cumulative Actuals] and [Budgets]) and desired dimensional attributes (e.g., ‘Calendar’[Month Year]), filter the visual based on the desired account (e.g., ‘Financial Statements’[Description] or ‘Chart of Accounts’[Description] = Cash, AP, Inventory, and AR ), and choose your visual type. That’s it! No code required. The model and metrics do the heavy lifting for you.

Now that all the visuals are built, you will need to design them in a way that articulates a story and delivers your narrative. Two great resources for building dashboards are the following: Power BI Dashboard Design Video Course, SQLBI and Designing effective report in Power BI, Kurt Buhler.

My final output looks like below, but feel free to create the metrics and visuals that tell your story!

Bonus Power BI Features

Below are features that I deployed to enhance user experience. I won’t go in depth about deploying them here, but I will reference some good material so you can add them to your reports!

Power BI Canvas Backgrounds

Adding custom canvas backgrounds can improve both the performance and aesthetics of your report! If you don’t have one, consider taking some time to craft your own. Here is a good reference: Power BI Background Design, Data Training.io

Filter Page and Breadcrumbs

Consider consolidating your filters into a synchronized and consistent format across your reports with filter pages and breadcrumbs. Filter pages can increase performance as visuals hidden in the background do not render as filters are being applied. Also, consider breadcrumbs to highlight important user selections. Some good references: Build a Slicer Panel in Power BI, Guy in a Cube and Displaying filter context in Power BI Tooltips, SQLBI.

Dynamic Filtering (Past Three Months and Full Year)

This is a neat visual to show the past three months of data, as well as the full year in the ‘totals’ section. The visual is accomplished with the following DAX measure that checks granularity which then applies either a yearly or monthly calculation (monthly only shows last three available months):

Actuals Past3M & CurrYear = 
VAR __LastVisible_Date = 
    CALCULATE( 
        LASTNONBLANK( 
            ALL('Calendar'[Date]), 
            CALCULATE(	
                [Actuals] 
                , ALL ( 'Financial Statements' )
                , KEEPFILTERS( TREATAS( {"Cashflow Statement"} , 'Financial Statements'[Financial Statement] ) )
            )
        ) 
    )
VAR __LastVisible_FirstOfMonth = DATE ( Year( __LastVisible_Date ) , Month ( __LastVisible_Date ) , 1 )
VAR __LastThreeMonths = 
    CALCULATE(
        [Actuals] , 
        FILTER(
            VALUES('Calendar'[Date] ) 
            , 'Calendar'[Date] <= __LastVisible_Date
                && 'Calendar'[Date] > EDATE( __LastVisible_FirstOfMonth , -2  )
            )
    )
VAR __CountMonths = COUNTROWS( VALUES( 'Calendar'[YYYY-MM] ) )
VAR __Yearly = 
    CALCULATE(
        [Actuals]
        , All( 'Calendar' )
        , TREATAS( {SELECTEDVALUE('Calendar'[Year])} , 'Calendar'[Year] )
    )
RETURN IF ( __CountMonths <> 1, __Yearly , __LastThreeMonths )

The final resolution gives the following:

The following articles walk through granularity as well as a similar pattern for showing prior six months of data based on a single slicer: Working below a DAX formula’s granularity, SQLBI and Show previous 6 months of data from single slicer, SQLBI.

Sorting Repeating Values

Within the ‘Financial Statements’ table, you may have the same category within different financial statements. The difficulty is that they may require different orderings based on the financial statement. To accomplish this task, you will need a custom column with embedded zero-width characters. I applied the following M-code to my Financial Statements data to procure the column that I put on the visuals:

= Table.AddColumn(#"Changed Type", "Detail Sorted", each Text.Repeat(Character.FromNumber(8203), [Detail Order]) & Text.From([Detail]))

Then, I simply change the name to ‘Detail’ once I add this column to the visual.

You can learn more about a similar implementation here: Built-in column sort order in Power BI, XXL BI

Conclusion

Accounting and Finance are historic disciplines with well-defined rules that are well suited for business intelligence modeling and reporting. We hope you have taken away fundamental understandings from Finance, Accounting, and Business Intelligence to craft your own beautiful financial reports in Power BI.

Below are the final screenshots of the financial reports.

Downloadable Content

Downloadable report available via our store!
Free download coupon code: Financial-Reporting-in-PowerBI-202310

This article was created & published by William Rodriguez, President and Principal Consultant at Analytical Ants

Reach out to Analytical Ants LLC for all your data/analytical needs. We specialize in #DataEngineering #DataScience, and #DataAnalysis.

Book Consultation
Book Consultation
Shopping Cart