Save content
Have you found this content useful? Use the button above to save it to your profile.
Expenses
iStock_Peshkov_AW

Your Power BI P&L report: How to handle expenses as positive numbers

by
7th Aug 2019
Save content
Have you found this content useful? Use the button above to save it to your profile.

If you view expenses as a negative number, then creating a P&L statement in Power BI is simple. But for many businesses it's not that simple. If expenses are represented as a positive number, it becomes somewhat trickier.

As an engineer, if you tell me that income is a positive number, then I would tell you that expenses are negative. That is logical, is it not? If you are happy with this, then creating a P&L statement in Power BI is easy.

Simply drop your P&L nominal groupings and accounts into a matrix, along with their transactions for a given period and, presto, you have a P&L statement. 

 

How it’s done 

The net profit of £56,733 is the total of all the transactions on the P&L accounts in the selected period, calculated by the Power BI matrix visualisation. This is very simple. 

My transactions are in a table called ‘TransactionLines’. My nominal records and their groupings of Sales, Purchases, Direct Expenses and Overheads are in a related table ‘NominalRecords’. 

The “Actual” measure in the above chart is: 

NominalRecords[IsBalanceSheet] is simply a True/False flag for each nominal record to determine whether a nominal account is a balance sheet account.

The totals problem if you represent expenses with positive numbers 

For many companies it is not that simple, as they represent their expenses as positive numbers. To achieve this in Power BI, you may consider simply multiplying your expenses by minus one. 

You could create a measure “Actual(+ve) like this: 

 

You end up with a result like this, which is ok for the rows of the matrix, but clearly wrong for the total net profit that should be £56,733 and not £380,938. 

 

One workaround would be to switch off the totals option in the matrix and display the net profit in a separate visualisation. This has the advantage that you can control the formatting of the total separately, but it would be quite tricky to get it to look right. You may end up with something like this: 

 

How to solve the totals problem within the matrix visualisation

If you want to display  the correct total within the matrix, then the trick is to calculate the total in one way and the rows in another, based on our two formulae [Actual] and [Actual(+ve)].

Fortunately, we can do this because Power BI independently calculates each value that a visualisation displays. You can read more about this in a recent article that I published recently “An introduction to the Filter Context”.

In that article, I explained how a Power BI visualisation separately calculates each value displayed, ignoring all other values. So Power BI does not calculate the incorrect net profit value of £380,938 by summing sales, purchases, direct expenses and overheads.

Rather, it applies our formula [Actual(+ve)] to our transactions, but without the filters for sales, purchases, direct expenses and overheads. Therefore, if we can detect whether or not a calculation has a filter applied to it, then we can identify that we are trying to calculate the total (in this case Net Profit) and in this case switch the formula to our original [Actual] that gives us the correct net profit amount.

There are a couple of DAX functions that we can use to solve this problem; ISFILTERED and HASONEFILTER 

The DAX ISFILTERED function 

The DAX ISFILTERED function detects whether or not a column has a direct filter on it. The syntax is simple: ISFILTERED(<columnName>) 

The function returns TRUE if there is a direct filter on the column and FALSE if not. 

In our case, the column that we want to look at is the NominalRecords[Group] column that we are using in our P&L statement (for presentation purposes this has a label “Profit & Loss” in the visualisation).

For each of the four rows in the matrix of sales, purchases, direct expenses and overheads, there is a direct filter for NominalRecords[Group] = “Sales”, “Purchases” etc. For the total (in this case labelled “net profit”), there is no direct filter. 

 

Using this knowledge, we can create a new measure that tests for this condition and switches between our two measures [Actual] and [Actual(+ve)] depending on the result. We will call this measure [ActualDisplay]. 

 

Our new P&L statement now looks like this: 

 

Code Options 

The example above uses the IF function to switch between the measures. You could equally use SWITCH as follows: 

 

In such a simple case, there is no strong argument for IF over SWITCH or vice versa. In which case, I would use IF, since I believe that it would be more widely understood. 

The problem with ISFILTERED in this case 

If we now decide to change our P&L visualisation to show gross profit (by excluding overheads), we end up with a problem: 

 

 

The thing is, our total “gross margin” calculation now has a direct filter on it that includes only “sales”, “purchases” and “direct expenses”. All of the rows in our matrix, including the total now have at least one direct filter and the “ISFILTERED” test will always return a TRUE result.

The DAX HASONEFILTER function 

Instead of ISFILTERED, we can use HASONEFILTER. The two functions are very similar, but HASONEFILTER returns TRUE if the column has exactly one direct filter applied and FALSE if not. The syntax is as follows: 

HASONEFILTER(<columnName>) 

In our case, no matter what we do with external filters and slicers, each row in our matrix always has just one direct filter. The total on the other hand will only have one direct filter if we have filtered the visual down to show just one row (in which case the total displayed will be the same as the row and this would be correct).

In all other cases, the total will have either no direct filters, or more than one direct filter. In both cases, we can apply an alternative calculation and get the correct result. 

The image below shows the two alternatives using ISFILTERED and HASONEFILTER, with a visual=level filter on the NominalRecords[Group] to calculate the Gross Profit. 

 

 

You can see that HASONEFILTER returns the correct result. The formulae are identical, but instead of using ISFILTERED, we use HASONEFILTER. 

 

Summary 

You can present a P&L statement in Power BI that displays expenses as positive numbers, yet still gets the total net profit amount correct. You can use ISFILTERED or HASONEFILTER to switch between alternative calculations depending on whether you are calculating a row value or a total value.

Based on the scenarios above, I would recommend using HASEONEFILTER as it will also allow you to filter the visual to display gross profit, or sales/purchase pairs of nominal records. I have presented both options though, as you may well think of an alternative situation where ISFILTERED would be better. 

Useful links 

DAX function reference 

Power BI Basics: an introduction to the filter context 

Using CALCULATE to modify the Filter Context 

Replies (10)

Please login or register to join the discussion.

avatar
By rkramer
20th Feb 2020 19:21

Hugh,

Great article! Very helpful as there are not a ton of resources on this topic out there. Would you guys be able to update the photo links though? It appears as though they have broken.

Best,
Ryan

Thanks (0)
Replying to rkramer:
Hugh Johnson
By Hugh Johnson
06th Jan 2021 17:31

Hi Ryan,

I am so sorry, I don't know why I didn't get a notification of your reply. Anyway (somewhat late), I will let the folk at AW know and, hopefully, we can fix this.

Regards,

Hugh

Thanks (0)
Replying to rkramer:
Hugh Johnson
By Hugh Johnson
06th Jan 2021 17:36

Ryan,

While that gets fixed, here is a PDF of the original article. I have since, changed how I do this, but the basic principles remain the same.
https://accountinginsights-my.sharepoint.com/:b:/g/personal/hugh_account...

Thanks (0)
Replying to hughjohnson:
avatar
By rkramer
06th Jan 2021 19:02

Thanks for this!

Cheers,
Ryan

Thanks (0)
avatar
By Wilhelm
15th Mar 2020 23:40

Thanks for a great article, Hugh - what you're doing makes sense from a text point of view, but it would be great if we could see the images. It seems they've gone AWOL - would you be able to update them anytime soon, or perhaps send the article as a PDF on the mailing list? Thanks!

Thanks (0)
Replying to Wilhelm:
Hugh Johnson
By Hugh Johnson
06th Jan 2021 17:31

Hi Wilhelm,

I am so sorry, I don't know why I didn't get a notification of your reply. Anyway (somewhat late), I will let the folk at AW know and, hopefully, we can fix this.

Regards,

Hugh

Thanks (0)
Replying to Wilhelm:
Hugh Johnson
By Hugh Johnson
06th Jan 2021 17:36

Wilhelm,

While that gets fixed, here is a PDF of the original article. I have since, changed how I do this, but the basic principles remain the same.
https://accountinginsights-my.sharepoint.com/:b:/g/personal/hugh_account...

Thanks (0)
avatar
By michellepace
10th Aug 2020 14:07

Hi Hugh, I'd really like to read your article but the images are all broken. I've tried on Edge, Chrome and Safari. Could you please fix them and let me know? I really need this article :) - thanks in advance for writing on it!

Thanks (0)
Replying to michellepace:
Hugh Johnson
By Hugh Johnson
06th Jan 2021 17:32

Hi Michelle,

I am so sorry, I don't know why I didn't get a notification of your reply. Anyway (somewhat late), I will let the folk at AW know and, hopefully, we can fix this.

Regards,

Hugh

Thanks (0)
Replying to hughjohnson:
Hugh Johnson
By Hugh Johnson
06th Jan 2021 17:37

Michelle,

While that gets fixed, here is a PDF of the original article. I have since, changed how I do this, but the basic principles remain the same.
https://accountinginsights-my.sharepoint.com/:b:/g/personal/hugh_account...

Thanks (0)