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:
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:
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.
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.
About Hugh Johnson
I am a founder director of Accounting Insights Ltd, a specialist provider of Power BI reporting solutions to accountants in practice and in industry. I help accountants to use Power BI to create intuitive, engaging reports from their accounting data. I deliver management packs, sales reports & forecasts, liquidity & cash flow reports, stock reports, and accounts receivable analysis from Sage, Xero, Exchequer, Microsoft Business Central, Iris, and CCH.
I hold a Master of Engineering from City University London (awarded with commendation and the highest marks in my year) and a Post Graduate Diploma in International Selling from Dublin Institute of Technology (awarded with distinction and the highest marks in my year).
My personal passions are high-performance Fireball dinghy racing and food.