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

[amitchandak]

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

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

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