Power BI Formatted Financial Statements with Custom Sub Totals
The profit and loss statement is an important financial report that illustrates a company’s revenue, expenses, and profit or loss over a period of time that is outside of the annual report. A profit and loss statement, also known as an income statement or P&L statement, provides a snapshot of a company’s financial performance, and as such is very useful for analyzing a company’s profitability and managing its financial health. Profit and loss statements usually contain information about a company’s sales, costs of goods sold, operating expenses, and taxes, in addition to other data. In order for investors and managers to know if a company is making a profit or incurring a loss, they compare the company’s revenues to its expenses. Based on the information obtained from this report, a decision can be made about how to improve the company’s financial performance in order to meet its financial objectives.
Formatting financial statements in business intelligence tools can be a challenge. This is due to the fact that it requires custom subtotals, blank rows, and hiding text in some places. Profit and Loss statements, Income statements, Balance sheets, and other financial statements require tricky formatting in order to be properly understood.
To handle these challenges we will use extended dimension, and measure’s based conditional formatting. I’ve previously blogged about the use of extended dimensions, including displaying the total on the bar visual and unselected values, among other things.
I have uploaded the data that is required for this blog to the GitHub repository. We have two important sheets that have the main data for the project, a GL sheet, and an account sheet. Both of these sheets are very similar to what you have as a source. In the display sheet, you will find the display that you need along with additional accounts and calculations that are needed.
Below are some screenshots of the data that illustrate how it looks
The extended dimension PL account is also created in the Excel sheet in this case. You can notice some accounts with blank names (with spaces). And the Order_ID column will be used to sort the account column. There was an issue with loading the blank accounts because the space in the blank accounts was removed during the loading process. I first loaded all other accounts except those with blank names. Following that, I marked the order_id column in the account as the sort column for the account. The PL account has finally been reloaded, with blank accounts in it. In this way, I was able to avoid the sorting error where more than one value.
This is what the initial power bi model looks like:
Create a “many to many” join between the Account account and the PL account. Maintain the direction of filtering from the PL account to the account in this case.
The account of extended dimensions will be used in the display of the data. This will be sufficient to meet the purpose of what account we are looking for. However, it does not solve the problem of a custom calculation that we require.
In order to calculate a custom total we need to use the measure, a measure that takes into account the values of the extended dimension in calculating the custom total.
We created the following measures
PL Amount = Sum(GL[Amount]) +0
PL Total = SWITCH(TRUE(),
Max('PL Account'[Order_id]) =3, CALCULATE([PL Amount], filter(all('PL Account'), 'PL Account'[Order_id] in {1,2})),
Max('PL Account'[Order_id]) =10, CALCULATE([PL Amount], filter(all('PL Account'), 'PL Account'[Order_id] in {5,6,7,8,9})),
Max('PL Account'[Order_id]) =11, CALCULATE([PL Amount], filter(all('PL Account'), 'PL Account'[Order_id] in {1,2})) - CALCULATE([PL Amount], filter(all('PL Account'), 'PL Account'[Order_id] in {5,6,7,8,9})),
Max('PL Account'[Order_id]) =15, CALCULATE([PL Amount], filter(all('PL Account'), 'PL Account'[Order_id] in {1,2})) - CALCULATE([PL Amount], filter(all('PL Account'), 'PL Account'[Order_id] in {5,6,7,8,9,13,14})),
Max('PL Account'[Order_id]) =18, CALCULATE([PL Amount], filter(all('PL Account'), 'PL Account'[Order_id] in {1,2})) - CALCULATE([PL Amount], filter(all('PL Account'), 'PL Account'[Order_id] in {5,6,7,8,9,13,14,17})),
[PL Amount]
)The following measures have also been created for matrix visual and table visual conditional formatting. And they are used in cell elements for conditional formatting using the field value option for background and font conditional formatting.
PL BK Color = SWITCH(TRUE(),
Max('PL Account'[Order_id]) in {3,10, 11,15,18 }, "Yellow",
Max('PL Account'[Order_id]) in {4,12,16} , "White"
)
PL Font Color = SWITCH(TRUE(),
Max('PL Account'[Order_id]) in {3,10, 11,15,18 }, "Black",
Max('PL Account'[Order_id]) in {4,12,16} , "White",
"Black"
)
// Additional measure creating for conditional fornt formatting of table visual
// This will hide few rows using font color
Color Font Category = var _min = minx(filter(ALLSELECTED('PL Account'),
'PL Account'[P&L Category] = max('PL Account'[P&L Category]))
, 'PL Account'[Account_ID])
return
if( Max('PL Account'[Account_ID]) = _min , "Black", "White")This is how the matrix will look like
with the following rows, columns, and values
Table visual with the filter of a year
with following columns
There is now a need to create two columns in P&L. In order to achieve that, we need to separate out the income accounts and expense accounts and have sort indexes set up for each.
For this purpose, we have added additional columns to the PL account. Disp Col will decide where the account should be placed, the income side or the expense side. The display determines the order in which accounts are displayed.
This is how the Matrix visual will look. Only PL BK color has been used for the background
Cell Elements -> Background Color
The table visual is a bit different. It will use the display for grouping along with the following measures
Expense Account = CALCULATE( MIN('PL Account'[Account]) , FILTER('PL Account', 'PL Account'[Disp Col] =2))
Expense Measure = CALCULATE( [PL Total], FILTER('PL Account', 'PL Account'[Disp Col] =2))
Income Account = CALCULATE( MIN('PL Account'[Account]) , FILTER('PL Account', 'PL Account'[Disp Col] =1))
Income Measure = CALCULATE( [PL Total], FILTER('PL Account', 'PL Account'[Disp Col] =1))The following color measures are used
Expense BK Color = SWITCH(True(),
Max('PL Account'[Display]) in {6,8} , "Yellow")
Expense Font Color = SWITCH(True(),
Max('PL Account'[Display]) in {7,9,10} , "White")
Income BK Color = SWITCH(True(),
Max('PL Account'[Display]) in {3,7,10} , "Yellow")
Income Font Color = SWITCH(True(),
Max('PL Account'[Display]) in {4,8} , "White")Color setting- Income Account
Background Color- Income Measure
Font Color- Income Measure
Color setting- Expense Account
Background Color- Expense Measure
Font Color- Expense Measure
This is how the table visual looks like
You can find the videos below. P&L
2 Column P&L
Free Microsoft Power BI Course for Beginners, 2023
My Medium blogs can be found here if you are interested
Click here to access all my blogs and videos in a jiffy via an exclusive glossary using Power BI. Please like, share, and comment on these blogs. I would appreciate your suggestions for improvement, challenges, and suggestions for topics so that I can explore these in more depth.
In addition, I have over 500 videos on my YouTube channel that are related to Power BI, Tableau, and Incorta. With the help of these videos, you will learn hundreds of DAX and Power Query functions, in addition to hundreds of Power BI, use cases that will assist you in becoming an expert in Power BI. Make sure you subscribe, like, and share it with your friends.
Master Power BI
Expertise in Power BI
Power BI For Tableau User
DAX for SQL Users
Learn SQL
