Power BI Formatted P&L with Custom Sub Totals and Blank Rows

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]) +

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"

)

More

// Additional measure creating for conditional fornt formatting of table visua
// 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")l

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

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